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?

Datasets

Data Investigation

Database Connection

Accepted answers

All comments

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

Sheet 2 to input.yxmd

@ravikumar060987

Hope this helps!!!!

Please let me know if you have any questions.

Sheet 2 to input.yxmd

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.

Input 30 sheets.png

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

@ravikumar060987 One way of doing this with the batch macros

CombineSheets_1213.yxzp

ravikumar060987

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

@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

CombineSheets_1213.yxzp

ravikumar060987

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

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.

Hi @ravikumar060987

Please find the expected output.

The below workflow helps to get the expected output:

Hi @ravikumar060987

Step 1: Directory tool, to read the full path of the file.

Step 2: Formula tool, to read only sheet2.

[FullPath]+'|||Sheet2'

Step 3: Dynamic input tool.

Expected output:

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

Hi @ravikumar060987

FYI, the output will be read in the ascending order of the filename it read as per the directory tool.

If you need to change the order, use the any tool/method in which you need to sort and read the files.

The sorting is based on your need.

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15880

MarqueeCrew 13708

apathetichell 13703