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

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?

Input

Transformation

Accepted answers

Emil_Kos

Hi @znooredeen004,

The macro from this link is awesome:

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

ArtApa

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-Files-with-Different-Field/ta-p/13370

All comments

Emil_Kos

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

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703