Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Importing Text File with Fixed Lengths

Highlighted
5 - Atom

I currently have a .txt file with over 600 columns and a separate excel file that has column names and lengths for each field. Is there a way for me to use the excel file to define the fixed lengths for the .txt file when importing into Alteryx? 

 

I've tried reading it in as a fixed width text file, but it only gives the option of manually defining each field name and length on an interactive slide bar or importing a Flat ASCII file with the Field Settings. 

 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Sorry @al4416, but my solution only works with 599 columns.  Okay, I'm only kidding....

 

<flatfile version="1">
   <file
      path="\\Mac\Home\Downloads\Alteryx Challenge\Zero Output.yxdb"
      eoltype="crlf"
      />
   <fields>
	<field name="FIPS" type="VString" length="5"/>
	<field name="APN Unformatted" type="VString" length="45"/>
	<field name="Property Land Use Code Description" type="VString" length="60"/>
	<field name="Owner Occupancy Indicator" type="VString" length="1"/>

   </fields>
</flatfile>

A flat definition can be created by reading your data definition (maybe use excel or get bonus points by writing a workflow to build it).  Once you have this .flat file built, you can use it to read your file and parse the .txt file rather than hand-creating the layout.

 

Hopefully this helps you.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
5 - Atom

@MarqueeCrew What are you using to create the flat file? 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

It is embarrassing ...

 

<field name="FIPS" type="VString" length="5"/>

=CONCATENATE("<Field name """,A2,""" type=""",B2,""" length=""",C2,"""/>")

 

If you use excel as a table for Name, Type and Length, you can construct a formula to create the XML.

 

600 entries is pretty fast to cut/paste into a notepad where you avoid the interactive field definition tasks.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
5 - Atom

Cool, one more question. How to I enable "Allow Short Lines", "Allow Long Lines", and "Trim Spaces?

 

I'm getting the error "record 24 is short and File/Field Layout 'allowShortLines' isn't checked, but because I'm using field setting from a file, I can't check them in the settings. 

 

Thanks!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Read the file in as .txt with 1 or 2 fields defined.  Set all of the appropriate options (short/long) in the gui.  Now write a .FLAT file out using an output tool (need I tell you to sample the first 10 records?).  Then you have a perfectly (starting point) good .flat file from which to replace the field definitions.  All of the other options are set.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
5 - Atom

Thanks for the help!!

Highlighted
5 - Atom

Hi,

I am getting the same error for some files (i have 6 files with the same kind of data.) 3 files pass through fine. for the 3, I was getting the same error. I changed the "Short Lines Allowed" option and it worked fine.

Highlighted
5 - Atom

Hi,

I am getting the same error for some files (i have 6 files with the same kind of data.) 3 files pass through fine. for the 3, I was getting the same error. I changed the "Short Lines Allowed" option and it worked fine.

 

Kind of curious, why for some files, it didnt need and others It needed. Source and layout of each of the files are same. Content is different, i.e. transactions from different days.

 

Regards

Renjith

 

Highlighted
Alteryx Partner

Super helpful solution. I was able to resolve a very similar problem for a member of my company's Alteryx User Group.  Couldn't have done this without your solution.

 

The only trouble I had was re-configuring the Line Ending Style to be "Unix" instead of the default "Use Description File."  I also had to re-configure the short/long lines allowed, but that was straightforward because an Error prompted me to change.

 

 

Labels