Combining a tab from multiple excel files into one massive tab

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?

Workflow

Reporting

Join

Accepted answers

afv2688

Hello @kcoleman,

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

Regards

file_joiner.yxzp

All comments

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

Hello @kcoleman,

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

Regards

file_joiner.yxzp

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!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335