CSV import with differing column layouts

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.

Input

Accepted answers

BenMoss

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

StackedTablesParseV2.yxzp

All comments

derekbelyea

Here is one way to tackle this problem. I have used a single Excel file with varying matrix sizes but it is essentially the same as reading multiple CSV files.

There may be a more elegant solution ... usually is, but I prefer to keep things simple.

The result this produces looks like this:

Mixed CSV Layouts.yxzp

BenMoss

I've had a number of clients that have faced this issue and I always point them in the direction of this post. Within it there is a batch macro that should allow you to do this task with relative ease.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-different-Field/ta-p/13370

All you need to do is have the full paths of each of the files you want to input, perhaps using a text input tool or even the directory tool and then plug this into the macro.

Ben

jay_chang

The issue is not multiple files with different layouts - it's within the SAME file that I have different layouts. I found that macro but it doesn't seem to allow me to read in structures that change within a file.

jay_chang

how are you getting the flow to skip to the next table? I was thinking something similar but didn't want to hard-code the row # to start import at.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703