How can I club five excel files based on the sheet name?

I have five excel files, the file structure of all the excels is same but the caveat is that they have different sheets I want one output file which would club sheet based on sheet names.

Eg- Sheet A in all the five files should be clubbed , Sheet B in all the five files should be clubbed and so on. The final output would be one excel with all the sheet names.

File 1 and File 2 attached are the mock input files.

Thank you so much !!!

File 2.xlsx

File 1.xlsx

output file.xlsx

Input

Connectors

Batch Macro

Interface Tools

Accepted answers

binu_acs

@nishant_bs Obe way of doing this

CombineFile_100623.yxzp

nishant_bs

In ideal scenario I would need the data from multiple files and then club them based on the sheet names.
So for eg- If file 1,2,3,4,5 have tab A I need to club all rows (The schema is the same in all sheets of the same name)

Qiu

@nishant_bs

I feel @binu_acs is giving the solution correctly but let me also try anyway.
I use the macro from gallery first to ready all the data.

https://community.alteryx.com/t5/Community-Gallery/Read-All-Excel-Files/ta-p/897750

Then use a Batch Macro to output the data based on Sheet Names.

Read_All_Excel_Files.yxzp

All comments

binu_acs

@nishant_bs Obe way of doing this

CombineFile_100623.yxzp

nishant_bs

umm Thanks for your reply binuacs but I have multiple excel files. This solution only takes tabs from one excel.

nishant_bs

Qiu

@nishant_bs

I feel @binu_acs is giving the solution correctly but let me also try anyway.
I use the macro from gallery first to ready all the data.

https://community.alteryx.com/t5/Community-Gallery/Read-All-Excel-Files/ta-p/897750

Then use a Batch Macro to output the data based on Sheet Names.

Read_All_Excel_Files.yxzp

binu_acs

@nishant_bs My solution should work for multiple excel files, all you need to mention the folder location where your files are saved, if you have sub folders check the include sub directory option in the directory tool

nishant_bs

Thanks both the solutions worked!! Thanks a lot.

Qiu

Glad it helps and thank you for the feedback

kishore02

Hi Qiu

I have small doubt in Alteryx can you please help me out in this.

step 1: I have connected data from folder A in share point using Alteryx share point input tool

step 2: using SharePoint output tool I have got output from another folder B

so now i want to delete the folder A automatically when the data is copied to output folder B in share point using alteryx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703