CSV import with differing column layouts

[jay_chang]

I have a data file that is in CSV format.  It is basically equivalent to multiple tables stacked one after the other, with typically a blank row or two separating each table.  The tricky part is that the tables can have varying numbers of columns like:

	H1	H2	H3
Row1	a	b	c
Row2	d	e	f

	H4	H5	H6	H7	H8
Row5	1	2	3	4	5

When I read the CSV into Alteryx, it assumes that the format will follow the layout of the first table and doesn't pull in the additional columns of the 2nd table.  I have roughly 15 tables that are stacked in this manner.  Ideally, I'd like to read all the columns of all of the tables into a single Alteryx table and then manipulate the data via Alteryx.

Is there any way to do this easily?  I can say that each table will be either 12 or 13 rows of observations plus a header row for each table.  I'd prefer not to use a SKIP N tool (CReW macro) if at all possible.

TIA.

[jay_chang]

@BenMoss so I'm running into an error when I use your flow against my data.  The error is "too many fields in record #16, which is the point at which my first table switches over to my second table.  Does it matter that I'm using a CSV rather than an excel file?

I'll try to post a redacted version of my data so you can see what I'm working with.

[BenMoss · Accepted solution]

In that instance I would start the import by brining the .csv data in with no delimiter, you can do this by selecting your file as normal and then using the delimiter \0 (no delimiter). 

You can then use the text to columns to to parse the data into the different columns, specifying the appropriate delimiter (likely comma) and the number of columns would be representative of the table with the largest number.

I've attached a new example with this logic.

Ben

[jay_chang]

Thanks Ben - this looks like it's working.  The placement of the columns is a bit funky and I'll need to double-check to figure out how to position the columns correctly, but it gives me a starting point.  Appreciate all the help.

[derekbelyea]

This looks like a case where you might want to go upstream (if you can) to where the multi-table CSV is created.  I would try to negotiate for 15 CSV files rather that one CSV file containing 15 non-orthogonal (is that a word?) tables. That would be more manageable for Alteryx to consume.