Combine Multiple Excel Worksheets into Single Excel

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

del superstore a.xlsx

del superstore b.xlsx

del superstore c.xlsx

superstore final.xlsx

Input

Data Investigation

Accepted answers

shancmiralles

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 ..

griffinwelsh

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

Example.yxzp

All comments

shancmiralles

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

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

Example.yxzp

DelData

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

Nice Work 😀

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703