Combining the Excel files with number of sheets

[ravikumar060987]

Dear All,

I have a situation, in a folder have 30+ xlsx files. Need to combine all and where each excel file has 2 sheets.

Sheet 1: Summary - not to combine
Sheet 2: Actual Data (Which has 12 common headers across all the 30+ files).

How can I do this in Alteryx without using a macro?

[ShankerV]

Hi @ravikumar060987 

Please use the directory tool, this will bring in the path of all 30+files and file names.

Then use Dynamic input tool by selecting the actual data from Sheet 2 will be sourced.

By this you can eliminate the sheet Summary to be read in the input.

Many thanks

Shanker V

[ShankerV]

@ravikumar060987 

Hope this helps!!!!

Please let me know if you have any questions.

[PanPP]

Hi @ravikumar060987 

You can use an input tool with a wildcard character.

If they have a similar naming convention, you can copy that directory and format the Input tool to contain a .xlsx file extension after selecting one of the excel files and picking a sheet. 

Hope this helps. If it does please like the post and mark it as a solution.

[binuacs]

@ravikumar060987 One way of doing this with the batch macros

[ravikumar060987]

Post that - how can I combine all the data. can you pls share that too.

[binuacs]

@ravikumar060987 The batch macro will be combining all the records into one excel, you need to add an output tool after the second batch macro and configuration

[ravikumar060987]

@ShankerV - Can you please share the next step too. How can I combine the data's?

[ShankerV]

Hi @ravikumar060987 

Taking an example to explain how to combine the data.

I have created some samples to explain.

Created 2 test files.

Test1 sheet2 contains the below data's

Test 2 Sheet2 Contains as below.

[ShankerV]

Hi @ravikumar060987 

Please find the expected output.

The below workflow helps to get the expected output: