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

TomWelgemoed

Hi,

I think I've got the solution now - another small (important!) missing piece from the problem description was that you only need the values from the 3rd column (C) - that means an iterative macro is not necessary and you can do a crosstab. 

The solution will then look as below. The trick is to tell Alteryx what the row numbers are in each report so that it consistently groups each line correctly. Note that in one of your files (TUHU) there is an inconsistency, so the columns are slightly offset in that case. That will happen in each case if any one of your 200 files are not 100% consistent. It should be easy to fix once the data is together though.

I'm attaching it for now - once you have downloaded please let me know so I can delete it at my end.

If this does work - please mark this as a solution? Took me a while! 😉

Solution 11_v3.yxzp