Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Importing Text File with Fixed Lengths

al4416
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. 

 

 

 

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
al4416
5 - Atom

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
al4416
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!

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
al4416
5 - Atom

Thanks for the help!!

grenjith7
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.

grenjith7
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

 

clayconnell
5 - Atom

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