How to import a variable column width, stacked csv file into Alteryx.

[mutuelinvestor]

I have a variable column-width stacked csv file that I'm trying import into Alteryx.  A brief file description follows:

The file is a csv file that includes several different tables.  Tables are differentiated by the first column of every row:

• H - Header row - only one row and seven columns.   ** Note - this is label a header row, but is really a data row **
• R - Race rows - typically 8 to 10 rows and approxiimately 69 columns
• S - Starters rows - typically 80 to 90 rows and approximatey 90 columns 
• E - Exotic Wager rows - typically 25 to 35 rows and approximately 9 columns
• F - Footnotes - typically 100 to 130 rows and 4 columns

The file also do not include a traditional title/header row (data in first row). The file is a standard txt file but comes with a .chart file extension.  I'm hoping that I won't have to edit the extension.  Note: To upload my sample file I had to change the extension from .chart to .txt. 

I have attached a sample file for your information. 

My objective is to import the file and then create a table in my SQl server database from the imported data without converting to an Excel file format.(xlsx).   I want to avoid the conversion to excel to maintain a fully automated work flow.  I will be processing files in rather large batches and converting each file to excel is not feasable.

I've tried importing using the text import tool and a couple of others, however I have not been able to import the entire file claenly.  Problems I have encountered include just importing the H header row (first row of the file).  

Has anyone come across a similar file structure and been able to import to Alteryx sucessfully.   I'd appreciate any suggestions or ideas you have. 

[chris_love]

Hey

So some tips:

- you shouldn't need to change the extension, import it as a txt file type
(show All Files at bottom) and choose no delimiter, this will bring
everything as one column of text. Untick the "First Row Contains Field
Names"

- create a new formula LEFT(Field1,1) to parse off the first character

- split off (using a series of filters) each type of table so you can parse
them separately e.g. using an expression [RowType]='E'.

- use Text to columns to split each type into the desired number of columns
by splitting on comma

- use Select or Dynamic Rename to rename the headers in each case.

I haven't done this for you, I imagine you want to learn yourself, but yell
if you want some more specific advice of just a final module and I'm sure
someone will help.

Chris

[s_pichaipillai · Accepted solution]

To add to chris for Text to cloumns

you can try the one here to split the number of columns 

http://artisantechno.com/index.php/2015/10/06/alteryx-part-2-parsing-semi-structured-data/ 

does same but you need to add /modify little to your requirement (Learn yourself by modifying it)

[mutuelinvestor]

Chris,

Thanks so much for the tips. I plan to put them to good use this evening.  

Jim

[mutuelinvestor]

Thanks for bring this to my attention. This is great.