Combining a tab from multiple excel files into one massive tab

[kcoleman]

Just as the subject describes, I need to dump one tab from multiple files (it's going to be in the 100s) into one giant tab.

All of the tabs should be called the same if that helps.

It doesn't have to be pretty, I just need to combine in bulk.

Any advice on the best way to approach this?

[JarekSkudrzyk]

@kcoleman if there is only 1 tab per file, tabs are named the same and the structure of the data is the same (column names, order of columns, data types) you can use input tool with wildcard *.
If the structure is different you can use the combination of:

1. directory tool to get the list of files

2. batch macro to import list of sheet names (using input tool) - based on the list of files generated in point 1.

3. 2nd batch macro to import the data from the list of sheets (also using input tool) - based on the list generated in point 2.

[kcoleman]

Thanks Jarek. 

There are multiple tabs per file, I'm only combining one tab from each. The structure *should* be the same, but that's not guaranteed - some files have may begun to vary after years of use.

[afv2688 · Accepted solution]

Hello @kcoleman,

Please find here an example of 3 different ways to join the excel files.

Regards

[kcoleman]

Thanks afv,

Method 1 actually works perfectly for me - and outputting a combined file with everything in one tab as I wanted.

However I don't fully understand the use of the Dynamic tools? I plugged in one of my files in place of Fruit but I'm getting the below errors in each:

Apologies if this is me misunderstanding the basics of this tool - I'm relatively new to Alteryx!

[JarekSkudrzyk]

@afv2688 correct me if I am wrong - but this error @kcoleman is describing appears when the tabs' structures are different ("... has different schema ...")

[afv2688]

Hello @kcoleman ,

as @JarekSkudrzyk  is saying, the error is due the tabs having a different schema, that means that the different files have either different amount of columns, columns with names that do not match or files that have the same amount of columns, same names but the data type is different (double vs v_string for example).

You would need to see and check how the different files are structured and see which are giving the error.

This could be exhausting and in the end nothing really could be achieved, therefore here I recommend getting just the output from the macro which is the one that I suppose is not giving any errors for sure.

Regards

[kcoleman]

Thanks all. Aside from those errors, the output is exactly what I need. Much appreciated as always!