Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Importing a Fixed Width File

Charity_K_Wilson
10 - Fireball

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.  Unable to update more column details.jpg

 

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

 

Unable to upload a file to define the fixed Width.jpg

 

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?  

5 REPLIES 5
danilang
19 - Altair
19 - Altair

Hi @Charity_K_Wilson 

 

Can you attach your DelimitedTest.flat file? 

 

Dan

Charity_K_Wilson
10 - Fireball

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
19 - Altair
19 - Altair

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  

round to 1.png

 

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
10 - Fireball

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
5 - Atom

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

Labels