Importing Text File with Fixed Lengths

[al4416]

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. 

[MarqueeCrew]

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

[al4416]

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

[MarqueeCrew]

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

[al4416]

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 · Accepted solution]

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

[al4416]

Thanks for the help!!

[grenjith7]

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]

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]

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.