Combine tabs of different excel workbook with same names

Question

Hi All,

I have 4 excel workbook with 4 tab each .

Excel 1 - tab1 , tab2, tab3, tab4

Excel 2 - tab1 , tab2, tab3, tab4

Excel 3 - tab1 , tab2, tab3, tab4

Excel 4 - tab1 , tab2, tab3, tab4

Schema of all tab1 is same and all tab2 is same, and tab3 is same and tab 4 is same  but schema in one workbook tabs is different.

i am working to combine all tab1, tab2, tab3, tab4 in one workbook tab1, tab2, tab3, tab4 resp but not able to do.

Please helo,TIA

echuong1 · Answer

You can use a batch macro to load the data in. The macro will essentially process in batches, in this case per sheet. At the end, all of the data will be appended together.

You can see in the results that the macro ran 4 times, once per sheet in my dataset. The schemas do not matter because I have selected to append my records by field name (similar to a union with autoconfigure by name).

The macro in the attached is generic and should work for any dataset for this purpose. All you have to do is swap your input file at the very beginning. More information here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

Your use case is a little different since you want to do this but for multiple files. It is also achieved through a batch macro. This has actually already been created and packaged up into a tool! See here to download:

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

New Workflow1.yxzp