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?  

Delimited File.yxzp

danilang

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

Delimited File.yxzp