Join multiple excel files in a folder to a single sheet

[Aqureshi025]

Hi Alteryx Experts,

I want the alteryx flow. Can someone help??

I have 200+ excel files in a folder. Each excel file with different name.
Each File contains multiple sheets but one sheet in all files are with same name i.e Financial Statement

Column A of sheet “Financial Statement” Is same in all 200+ excel workbooks
I want to join all Financial Statements in a single excel sheet.

My output should be

Column A of my Output file should be picked up from any file in a folder as all sheets have same Column A.

In Column B,C,D.... i want the numbers which are in each file till 200+ columns. In Row i need the file name on top so I can identify that column is from which file.

I cannot use 200 times input tool and then use Join tool and then output tool.

My code work perfectly but it required many manual inputs tool and connections and setting in field

Please help

[Aqureshi025]

Hi.

It works finally. I need to select the files in the directory which have only the sheet name Financial Statements.

If there are other sheets in the workbook it give an error.

Thanks for solution

Regards

[TomWelgemoed]

Wow, that was tricky! Glad you got it working...well done!

I assume this is way easier than joining 200+ files 😉

Yes, I didn't think you had other files in the directory - sorry - so that would've saved us some trouble. Good you figured it out though.

[Aqureshi025]

Can we alter the code if the workbooks have other sheets in the file. So the code only pick the sheet "financial statements" and then give the output,

Because now the workbooks has only 1 sheet which is financial statements, if there are other sheets in the workbook, then it gives an error.

[TomWelgemoed · Accepted solution]

Yes, for that you need to create a macro to replace the sheet name dynamically.

If you want to open a new thread and give the full problem statement, we can work on that together.

[gsdutson]

I do not understand what you are trying to say. I am having the exact same issue. I need to combine multiple .xlsx files with multiple worksheets (all named the same) in one workflow so that I can drop in a new xlsx and refresh and have the new data

[TomWelgemoed]

Hi @gsdutson ,

The original thread is actually here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Join-multiple-excel-files-in-a-folder-...

Hopefully that helps you understand better! 🙂

Regards,
Tom

[ndani]

Hi, I have multiple files with same schema but the sheet names are different. How do I handle different sheetnames?

[TomWelgemoed]

HI @ndani ,

So sorry, I didn't catch your message a few weeks ago. Did you manage to resolve the issue? If not, please send me an example sheet so I can demonstrate?

Regards,

Tom

[simasarry123]

Hello, I am facing the same issue . I have tried to copy your workflow.

directory tool is linked to the folder where all files are saved, wild card selects .xlsx

dynamic input tool is linked to one of the sample file that is present in the folder which is connected to directory.

change full path selected. 

When I run the workflow, I get the error that the file has a different scheme than the first file in set. How to clear this ? I know all my files are identical with same header rows and same tab names. 

[hiroleen]

Perfectly worked with your example Solution 11 - thank you! (I cannot even LIKE it but I LOVE it)