Combining Multiple Worksheets with the Same Template from Excel Files into a Single Output

I have an Excel template that contains multiple worksheets, and all worksheets across multiple files follow the same structure (i.e., they have the same column names). I need to combine all these worksheets into a single dataset and export the result as an Excel output.

Could someone guide me on the best approach to achieve this in Alteryx Desktop

Workflow

Macros

Accepted answers

binu_acs

@AmudheshD updated workflow attached,

create a folder named output where your this workflow saved, (eg: if you are saving this workflow in a folder named "abc" then you need to create another folder inside the folder abc and rename to "output")

the workflow skips any records which is mentioned as "Give the employee ID" in the person ID field, if you are having different field in your original input file then you need to update that inside the macro, just open the macro and look for any filter tools and make the necessary changes

the output will be saving as Report.xlsx, you want to change the output name then open the macros and look for any formula tool where the name mentioned as report

Multiple_Excel_Output_18122024.yxzp

All comments

binu_acs

@AmudheshD one option is the bath macro for your use case

https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

AmudheshD

Hi Binuacs, Thanks for the response. I have tried this out but it seems that it doesn't work for multiple worksheets inside a same workbook. Could you please suggest an alternative

binu_acs

@AmudheshD attaching a sample workflow which will combine all the tabs into one output

Combine_Sheets_16122024.yxzp

AmudheshD

Hi Binucas - My scenario is that I have an excel template in which I will receive data from multiple users. I need to combine all the files using Alteryx flow, and the I need to extract it as the same excel template with matching sheet names merged after skipping a few rows before merge of each sheet

Example: If my excel template has 3 sheets named Sheet1, Sheet2 and Sheet 3 in 4 workbooks named Workbook1, Workbook2 and Workbook3 I need to merge all this after skipping a few top rows in each sheet.

I am attaching files for reference of input and desired output.

@AmudheshD here is the updated workflow. change the path in the directory tool also create a folder named output for the output files to be saved

Multiple_Excel_Output_16122024.yxzp

AmudheshD

Hi Binuacs, I appreciate. This works well but I need it to be dynamic. It is one of the templates that I will use. I have multiple templates with around 6 to 14 worksheets in them, Do you think it would be possible to have a flow that dynamically picks all the file if we define the template in the input tool?

binu_acs

@AmudheshD the given workflow is dynamic, all you need to do is put the template in a folder and update the path in the directory tool, there is no hardcoded values any where except that you need to create a folder called output, so that the output will be saved in that folder, if you want to update that open the macro and update in the formula tool

AmudheshD

Hi Binuacs, I am getting error because of the Person ID field that has been called as a variable in the Output sheets macro. I tried giving the field name of my Employee ID still it is not working. My Employee ID will be in the second column of each sheet. Can you please guide me how to do that?

binu_acs

@AmudheshD personal id use in the filter to exclude the unwanted rows, if you have a different value you can update the second names "macro output sheets macro" and update the filter condition

AmudheshD

Hi Binuacs the flow is working fine after I alter the filter thanks for the help. I could find that new sheets are not being created and only the first sheet is being appended and all other sheets are not found in the output. Also to add to the requirement, I need headers from first workbook and skip 5 rows in each other workbook before merging the file. Can you help me out in this alone!

AmudheshD

Hi Binuacs it is working once I change the variable but I could find that only one sheet is added to the output of the flow and all other are not present. Also I need to skip first 2 rows in all other files except the first file before appending them, Is that possible. Could you please help on that?

binu_acs

@AmudheshD i can see 3 sheets in the output file, are you saying you are not able to see the email sheet and mobile sheet in your output?

the workflow ignores the first 3 lines from each of the files, can you give me the sample input files and expected output file again with all your new requirements?

AmudheshD

Hi Binaucs,

Yes in my output file I am not able to see other sheets but only one sheet named as "name". I am attaching the files again here for reference.

Requirements:

1. Skip first n rows in all files except input1 before merging(Kindly mention where this step is being processed so I can change it dynamically in future if needed)

2. Output sheet to take first sheet as header without skipping any rows in it

3. New column in output sheet which will have the file name as Column A

Note: If you can document the steps it would be more helpful for me to understand the process as it is new to me to work with Alteryx and Macros in Alteryx

Thanks in advance

@AmudheshD updated workflow attached,

the output will be saving as Report.xlsx, you want to change the output name then open the macros and look for any formula tool where the name mentioned as report

Multiple_Excel_Output_18122024.yxzp

AmudheshD

Hi Binuacs,

The flow is working as expected but there are still a few things needs to be added. I need to skip first N rows in each sheet before merging. Is it possible to connect? I have pinged you.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703