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

[AmudheshD]

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

[binuacs]

@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

[binuacs]

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

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

[binuacs]

@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

[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?

[binuacs]

@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?

[binuacs]

@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