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:
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.
Solved! Go to Solution.
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
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)
Chris,
Thanks so much for the tips. I plan to put them to good use this evening.
Jim
Thanks for bring this to my attention. This is great.