Input Multiple .CSV with different rows and column headers

[R_L]

Alteryx Newbie here.

I have a multiple data file that are in a CSV format within the same directory folder.  

They are quite similar, but involve columns and sometimes different row headers.

Example:

CSV 1: FileName: FruitStore_Canada.csv

Fruits	Store A	Store B	Store C	Store D
Apples	Yes	Yes	Yes	No
Oranges	Yes	No	No	Yes
Banana	No	Yes	Yes	No
Pear	No	No	No	N/A
Grapes	No	No	Yes	N/A

CSV2 FileName: Fruit Store_USA.csv

Fruits	Store E	Store F	Store G	Store H	Store I
Apples	Yes	No	No	Yes	Yes
Oranges	N/A	Yes	Yes	Yes	No
Pear	Yes	No	No	Yes	Yes
Grapes	N/A	Yes	N/A	Yes	N/A

I am interested in combining them into one table. However, as they are a different schema - I've having trouble getting it to work. I've tried using a Batch Macro, however, it only "unions" and drops the first header row from the second CSV.

In the example CSV above, I know that there will definitely be different stores in the Column Headers. In-terms of the Row Headers, there will be some identical Fruits, but there may also be missing or extra Fruits as I add in more additional CSV as input in the future.

Ideally, I would like something like the below:

Fruits	Store A	Store B	Store C	Store D	Store E	Store F	Store G	Store H	Store I
Apples	Yes	Yes	Yes	No	Yes	No	No	Yes	Yes
Oranges	Yes	No	No	Yes	N/A	Yes	Yes	Yes	No
Banana	No	Yes	Yes	No	[Null]	[Null]	[Null]	[Null]	[Null]
Pear	No	No	No	N/A	Yes	No	No	Yes	Yes
Grapes	No	No	Yes	N/A	N/A	Yes	N/A	Yes	N/A

Any help appreciated! I think it's something I have to change in the batch macro, but I'm not too sure. Perhaps nested batch macros?

Thanks!

[DavidP · Accepted solution]

So does your batch macro present you with something like this?

In that case you can use a transpose and crosstab tool to convert it to this:

[Thableaus]

Hi @R_L 

The issue you reported building your batch macro is not a real problem. It's just because you probably did not connect your result with a browse tool (that's why only the first header row was showing up).

Anyways, I tried to simplify the batch macro and overcomplicated a little bit the workflow itself, but here's the solution that I thought.

WF appended.

Cheers,