Importing a Fixed Width File

[Charity_K_Wilson]

I'm trying to import a file using the Fixed Width option.  The file has no delimeters between the various data points.  

I tried going in and specifying the column width in designer.  I've got over 100 columns and it was able to get about half of them.  And then it quit working.  Any time I try to update the rest of the column names and width the cursor just stays at the end of the document.  I am unable to update any further names and width.  

Next I tried using a Flat File to define the fields.  But I have an error in that file.

I used the work flow from @JordanB  post https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-to-read-in-fixed-width-files/ta-p/4332

The error is weird because it says I have a "<" character in Line 7 position 20, and I don't.  I think that's the word Interest.  So, I can't figure out how to correct that error.

Are there any other options for reading in a Fixed Width column like this?  

[danilang]

Hi @Charity_K_Wilson 

Can you attach your DelimitedTest.flat file? 

Dan

[Charity_K_Wilson]

No, I can't attach the DelimitedTest.flat file.  The message board won't let me put it here.  

What I can do is include the workflow that generated it so that you can produce the Flat file yourself.

[danilang · Accepted solution]

Hi @Charity_K_Wilson 

There's a few things going on here.

1.  The Import Fixed Width File format.yxzp workflow actually produces 2 files DelimitedTest.flat and DelimitedTest.asc.  DelimitedTest.flat consists of the following

<flatfile version="1">
   <file
      path="T:\BEMI\Alteryx\Solutions\Importing a Fixed Width File\Import Fixed Width File format\_externals\1\DelimtedTest.asc"
      eoltype="crlf"
      />
   <fields>
      <field name="<flatfile version="1">" type="V_String" length="300"/>
   </fields>
</flatfile>

This is the file that contains the error when you try to import it.  It's the ">" right after version="1"

This file is  actually a pointer to the layout file, which is DelimitedTest.asc.  This is the file that contains your columns widths.  If you rename this file to .flat you'll be able to import it

2.  One of your columns contains an ampersand "&" in it.  Since the file is an xml file, you have to escape the "&".   It's line 90 in the .asc file.  Change it to 

From 
<field name="P&I Payment" type="String" length="12" />

To
<field name="P&amp;I Payment" type="String" length="12" />

3. I had to play with the record length field.  Even then it was hit and miss as whether the flat file would be completely imported or not  

I've attached the changed work flow and included the flat file that I modified in step 2 above.  It looks like you still have one more field to specify.  The last column "Currently Due Amount" has 2 values in it.

Dan

[Charity_K_Wilson]

Thank you!

That worked.  I had to fiddle with it because it turned out there was an undocumented column at the end.  

Pointing out that there was an "&" sign in one of the columns was the major insight that I needed to break thru.  I appreciate it very much.

[PeggyWitt]

Thanks so much danilang. This is my first time using a flat file to import the layout for a fixed length data file. It saves a lot of time & your instructions & examples were great!

Peggy