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

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

[binuacs]

@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

[binuacs · Accepted solution]

@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

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