Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

How to read in fixed width files

Alteryx
Alteryx
Created

We've had a few questions on importing fixed width files within Alteryx. Here is a quick 'how to' guide in reading in un-defined file formats!

 

File input: Ascii flat file or un-defined field settings

 

The catch: Alteryx needs a specific file format to read in the field names, type and length.

 

Example Layout:

<flatfile version="1">

                <file eoltype="lf" allowShortLines="t" allowLongLines="t" trimWhiteSpace="f" />

<fields>

<field name="Soccer" type="V_String" length="256"/>

<field name="American Football"·type="String" length="64"/>

<field name="Tennis"type="Double" length="8"/>

<field name="Lacrosse"type="String" length="64"/>

</fields>

</flatfile>

 

Module Attached: Attached is a workflow which creates this layout, all it requires is an input with field names, type and Length columns.  The workflow inserts the preset headers and footers to the file format (Underlined in the example format above). The module outputs a flat file (*.flat) which is an accepted format to read in non-defined file formats.

 

***There will be an error when you open the workflow due to 'no ouput being specified'. The output tool just needs to be configured to specify an output location to write too. 

 

Reading your fix width file: In a new module you can then input your ascii flat file and when prompted to read in the file, 'read in as a fixed width text file'. In the next window, select the radio button ‘Use field settings from file’ and then select the flat file you produced from the attached workflow. This will then update the flat file layout window with the file format you inputted from the generated *.flat file. You can then click OK and the file will be read in with the fixed width dimensions.

Attachments
Comments

Hi , 

 

I tried this and I get this following error?

These are the 2 files that were generated that i Intend to use as the layout for fixed width delimited file. 

 

fileformat.asc

 

<flatfile version="1">
<file eoltype="lf" allowShortLines="t" allowLongLines="t" trimWhiteSpace="f" />
<fields>
<field name="TransactionType" type="V_String" length="1" />
<field name="ProductCode" type="V_String" length="5" />
<field name="Option" type="V_String" length="3" />
<field name="Alternative" type="V_String" length="3" />
<field name="PolicyEffectiveDate" type="V_String" length="8" />
<field name="Premium" type="V_String" length="12" />
<field name="FILLER1" type="V_String" length="4" />
<field name="PolicyNumber" type="V_String" length="7" />
<field name="FILLER2" type="V_String" length="2" />
<field name="InvoiceNumber" type="V_String" length="13" />
<field name="BillingStartDate" type="V_String" length="8" />
<field name="InvoiceDueDate" type="V_String" length="8" />
</fields>
</flatfile>

 

file format.flat

 

<flatfile version="1">
<file
path="C:\Users\smadhushanka\Documents\BlueSun\Shadow Run\POST SALES\file format.asc"
eoltype="crlf"
/>
<fields>
<field name="<flatfile version="1">" type="V_String" length="300"/>
</fields>
</flatfile>

 

Many thanks

Sheron

 

 

 

 

Fixed file import error.png

Alteryx
Alteryx

Hi @sheron_madhushanka,

 

The .flat file should contain your field schema and not the file path name? 

 

When you read the file in within the input tool you should then select the.flat file you created using this workflow. 

 

The .flat file above is confusing me? Where were you looking at using this file? 

 

Best,

 

Jordan Barker

Solutions Consultant 

8 - Asteroid
8 - Asteroid

A couple of years ago, I wrote an article about the power of Alteryx for reading fixed-length flat files. Here is a link to the article: https://3danim8.wordpress.com/2014/10/03/a-truly-unbelievable-alteryx-flat-file-reading-result/

 

Ken

 

8 - Asteroid

Two things:

 

Firstly, Sheron, use the .asc file for defining your layout. The .flat doesn't work.

 

Secondly, Jordon, I am getting the following error when I try: "flatfile field lengths must be greater than 0." I know my module works for another flat file i do regularly. I'm trying to get the data from this source: http://ahrf.hrsa.gov/download.htm. I've downloaded the county level asc file, and am trying to get Alteryx to read it in and procude a yxbd so I can run analytics with it. But I just can't get it to work. I know there are data in the file, because I can open it in EmEditor, and I can see everything all laid out. But it doesn't seem to be recognized by Alteryx. If I tell Alteryx to read it in as a delimited file with spaces as the delimeters, it seems to see the data then (although of course it parses it incorrectly, because it's not actually a space-delimted file). Since you can download the data, think you could help me figure out what's going wrong and how to make it work?


Thanks!

Alexandra

Thanks Alikat!!

 

Using hte .asc file as for defining the layout worked like a charm.

 

Thanks

Sheron

Alteryx Partner

I'm so close to having this working.

I built the XML file, no errors.

 

Then I do the input, use the Flat File Layout and browse to the xml file.

 

But when I run the workflow, on the input tool I get this error -

"XmlParse Error: the attribute "scale" is missing"

 

flat file scale.PNG

 

 

What now?

Thanks.

Alteryx Partner

OK, I got this working.

 

I have a ton of these .asc files to import so this is going to be useful.

 

I had to edit the XML file by adding scale="0" to each field.

 

Now I think I will edit the provided workflow to add in the 'scale="0" to each row.

 

flat file scale added.PNG

6 - Meteoroid

I have a couple questions on using flat file:

  1. Is it possible to read multiple files when using flat file for loading fixed width files (Is there a wildcard option? I tried and it did not work)?
  2. Is there a way to read the actual file name mentioned in the flat file that is being processed so that I can append that to a column in my database?

 

Alteryx Certified Partner

I'm having some of the problems talked about here.    I'm getting the following error when I try to import the flat file to define my different fields, and widths in Alteryx.

Error message for reading in asc file to Fixed Width.jpg

 

So, I went and checked the xml code I'm producing.  The error said it was Character 20 in line 7.  The Character in position 20 on line 7 is "I", for the start of the word Interest Rate.  So, I'm at a loss.  There is no stray "<" character in the code.

 

Code for Fixed Width.jpg

 

Any recommendations?  How do I solve this so I can read in this huge fixed width file?

Alteryx
Alteryx

@Charity_K_Wilson 

 

Is an alternative to read this file in as a .csv and use the delimiter "\n" which would split the data onto new lines. You can the use text to columns to parse on " to get the values into the right columns. 

 

Best,

 

Jordan

Alteryx Certified Partner

@JordanB 

 

No, that didn't work.  I have 100+ columns that need to come in.  It's unable to split the columns.  Everything is ending up in column 1, and the other columns are nulls.  I really would prefer the Delimited File method where I specify the Fixed Width by uploading a file.  I tried going thru the data and specifying the fields, but I got about 100 of the fields labeled and it seems I've hit the upwards limit of that method.  It wasn't work for me any more.

 

Unable to parse.jpg