Combine Multiple Excel Worksheets into Single Excel

[DelData]

Hi There, Can you help?

I have 3 excels with related data but different in that they all have different numbers of columns....

I don't want to union these excels....cause when i do i get lots of blank columns since data in excels is not all the same

What i want to do is get Alteryx to combine the 3 superstore excels....del superstore a, del superstore b and del superstore c....into a single Excel called superstore final which contains del superstore a, del superstore b and del superstore c excels as separate worksheets.

I currently use Version: 2022.1.1.42673 Patch: 8

[shancmiralles · Accepted solution]

hope this helps @DelData 

hi! you want to connect your input tool to a block until done tool... then your output tool (be sure to name the sheet ) do this to all three files..
now you have to connect them.. via the 3 stop until done tools that you have... 
i first connect a select tool to the 3rd output anchor of the first block until done and be sure to uncheck/deselect all fields on the select tool..  then connect a union tool to this select tool.. the second input for this union tool will be the 1st output anchor of your 2nd block until done .. once you've connected the 1st and 2nd files via the union tool , connect a 2nd out put tool and naming the sheet by using the same file name..
do this one more time for the connection between the 2nd and 3rd data for the 3rd output ..

[DelData]

Hi.....thanks for update.....in this example I have 3 excels to join...other times it may be 10 or so....ideally i was looking at more scalable solution....i have tried a batch macro but failed.... 😥

[shancmiralles]

@DelData 

I see.. you can use the directory input tool .. .. combine it with a blob tool too..  that's the best way i can think of.. it's really tricky combining multiple excel sheets.. 

[griffinwelsh · Accepted solution]

I've attached a sample solution for you.

1. Use a directory tool to feed the filepaths into a batch macro. 

2. In your batch macro feed in the data for each sheet and output into a single file

[DelData]

Thank you for getting back to me...this is exactly what i was after....its a working batch file :)

Nice Work 😀