Combine multiple excel files with different file/tab names but same columns

[znooredeen004]

Hi All,

I have 40 excel files named this way:

Time Sheet Analysis - JohnDoe.xlsx

Time Sheet Analysis - MaryJane.xlsx

Time Sheet Analysis - SallyMae.xlsx

Each file then has one tab with their name one it. For example if you open JohnDoe.xlsx there will be one tab name JohnDoe. However each file has the same columns. 

What would be the best way to best way to combine all 40 excel files into one data sheet?

[Emil_Kos · Accepted solution]

Hi @znooredeen004,

The macro from this link is awesome:

https://gallery.alteryx.com/#!app/Read_All_Excel_Files/58dd51b3a18e9e18fca64172

[kelsey_kincaid]

Hi @znooredeen004 ,

If all of these files are in a single folder, you can use a wildcard in your input data tool to pull in all at the same time. It will skip any files that have a different schema. If this doesn't work for you, you can use a directory tool and a batch macro to combine the files as well.

[ArtApa · Accepted solution]

Hi @znooredeen004 - If the schema for all files is truly the same then the solution from @kelsey_kincaid is the best. Otherwise, please read the following article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi... 

[znooredeen004]

Hi @kelsey_kincaid - Unfortunately I don't think I'm able to use the wildcard because every sheet has a different name (unless I'm not configuring the input tool correctly)

[znooredeen004]

@ArtApa This also worked out very easily!