CSV Parse

[mauricio]

Hello guys,

I need some help with a file that has a very unnusual structure.

The data is as follows:

 

The first line of each "table" is the header.

I need to parse it into the following output:

Any help would be very much apreciated.

[alexnajm]

Text to Columns is your best bet! Split to 4 columns on the comma delimiter. Then a Dynamic Rename can move the first row of data up to the header row

[mauricio]

Thank you @alexnajm 

The problem is that there is 3 tables, with different schemas in the same file.

[alexnajm]

Then you'll need to split out the table with the differing schema, split it separately, and Union after

[mauricio]

Thank you @alexnajm, but I will need a more elaborated solution.

The example I posted is a simplification of a file with thousands of "sub-tables". The solution must be dynamic.

[alexnajm]

We cannot provide a dynamic solution if we don't know the full context of the data. For example, will it always be 3 or 4 columns, or could it be 5 columns? If we knew something like that, the solution may avoid the need for a macro...

Because you want the dynamic piece, if you need each individual table to parse and stack, look at a batch macro then - make it batch on each individual table, do the parsing that you need (likely a split to rows option of the text to columns tool followed by dynamic rename as mentioned), and make the macro auto configure by name so they stack the way you want. Think about how you would dynamically do it for one table, and that should be the majority of your batch macro

Here's an article to help guide you: Getting Started with Batch Macros (alteryx.com)

[alexnajm · Accepted solution]

Ok I caved - here you go. Please mark this as a solution - thank you in advance

[CoG]

@alexnajm, it took less than 20 minutes to cave! 😆

Very nice dynamic solution though!

[alexnajm]

I realized that the solution was going to be more complicated if it needed to be that dynamic, so it was a fun one to build @CoG :)

[mauricio]

Thank you @alexnajm that is what I was looking for