Error importing fixed width file

[recoilx]

Hi Everyone,

I have a text file that I am trying to create a workflow for.  My current process uses MS Access.  Back then I originally created a Fixed Width Importing Spec for the TEXT file since it was a monthly process.  Moving to Alteryx, I copied the Fixed Width Importing Spec from MS Access into an Excel (.CSV) Spreadsheet for the Alteryx Import Tool when setting up the import file.

I noticed that there were some differences, so I adjusted the MS Access Importing Spec to match the Alteryx Fixed Width format.

MS Access Spec Fields:

Field Name (renamed this to "Name" to match Alteryx Spec Field)

Start (removed this since Alteryx Spec Field doesn't contain)

Width (renamed this to "Length" to match Alteryx Spec Field) 

Skip (removed this since Alteryx Spec Field doesn't contain)

Type (added this column since it is required for Alteryx Spec Field)

Alteryx Spec Fields:

Name

Type

Length

Scale

However, I am getting an error when I try to import the Fixed Width Importing Spec Excel (.CSV) File.  The error reads as,

"There was an error reading "C:\Users\..............": Data at the root level is invalid.  Line 1, Position 1.

Any ideas as to what I am doing incorrectly?

[IraWatt]

Hey @recoilx, I maybe misunderstanding the problem but if the data is in MS Access can you not connect to it directly through Alteryx and skip the flat file step? Also CSV files are not fixed width they are just comma separated you should just be able to drag a csv onto the canvas without doing any fixed width steps.

To investigate the error I would right click on the file and open it in notepad and see if there is anything strange, for instance the first line of a CSV should be the headers separated by commas and the data rows below should have the same number of rows and also separated by a return character.  

[recoilx]

Not really.  I use the MS Access DB to take the text file extract out of the system to separate the data into columns.   Right now, I have to extract the data out of the system, run it through MS Access to get the data into appropriate columns and table format, then take that file and import it into Alteryx for workflow processing.

I want to bypass the entire MS Access process of formatting the system output into columns and use Alteryx for the entire process.

[IraWatt]

@recoilx ah okay, is the starting file a CSV? If so is there anything unusual when you open it in notepad such as any of the things I mentioned above?

[recoilx]

Yes it was a .CSV and the first line had commas like you thought.  Removing those commas didn't work however.  I think I figured it out though, in a text file I formatted the data like below and saved it as an .ASC file type.  I never heard of that file type before but it seemed to work.  I'm still playing around with it haha.

<flatfile version="1">
<file
path="data.asc"
eoltype="lf"
trimWhiteSpace="f"
allowShortLines="t"
/>
<fields>
<field name="EXBR " type="V_String" length="7" />
<field name="CUR" type="V_String" length="1" />
<field name="REV-FLAG" type="V_String" length="1" />

[IraWatt]

@recoilx great to hear you've almost got it working. Just to clarify there should be commas in the CSV by the sounds of the error the CSV file is malformed but without an example file its hard to say what exactly malformed.