topic How to import a variable column width, stacked csv file into Alteryx. in Alteryx Designer Desktop Discussions

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

mutuelinvestor — Thu, 24 May 2018 01:27:06 GMT

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.

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

chris_love — Thu, 15 Oct 2015 16:33:27 GMT

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

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

s_pichaipillai — Fri, 09 Oct 2015 18:10:12 GMT

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)

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

mutuelinvestor — Fri, 09 Oct 2015 18:53:12 GMT

Chris,

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

Jim

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

mutuelinvestor — Fri, 09 Oct 2015 18:55:52 GMT

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