on 09-23-2015 02:12 PM - edited on 07-27-2021 11:46 PM by APIUserOpsDM
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.
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
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
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
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
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"
What now?
Thanks.
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.
I have a couple questions on using flat file:
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.
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.
Any recommendations? How do I solve this so I can read in this huge fixed width file?
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
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.
I am facing an issue at the dynamic input - Error: Dynamic Input : XmlParse Error: the attribute "path" is missing. Given the paths correctly as well as correct .flat filess