Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Join multiple excel files in a folder to a single sheet

Aqureshi025
8 - Asteroid
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
29 REPLIES 29
Aqureshi025
8 - Asteroid

By running your workflow I get the data below to each other. means TUCF completed in row 50 for e.g then the next file start from row 51. 

 

I need to get the data as Column A is constant and then in Column B,C,D,E,F the numbers in the files of other excel sheets

TomWelgemoed
12 - Quasar

Yes, it wasn't clear to me that you wanted the reports joined together - I assumed you wanted to read from multiple files - I understand that better now.

 

The only way I can think of right now that this can be done is if you build a workflow that reads each file dynamically (like we've done) and in the next step you look to build an iterative macro to join each output together. You will use the output from what we've already built as the input for the iterative macro.

 

I'm out of time for the evening (past midnight!), but maybe try to give that a go yourself. I'll see how you got on in the morning.

TomWelgemoed
12 - Quasar

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.3.jpg

Aqureshi025
8 - Asteroid

Same error - No Sheet specified. I try to select the sheet in the data source folder and also tried in the separate folder.

 

Aqureshi025_0-1573802301183.png

Aqureshi025
8 - Asteroid

Sheet is there, nut why this error came up?

 

Aqureshi025_0-1573802449585.png

Aqureshi025
8 - Asteroid

Aqureshi025_0-1573802532695.png

TomWelgemoed
12 - Quasar

I'm not sure why you are trying to modify the template - it already is set to recognise the files you're importing.

 

You should only change the 1st tool, the directory tool.

Aqureshi025
8 - Asteroid

Even, I just change the first directory, no change in the dynamic input tool, the same error?

 

Is this creating the problem?

Aqureshi025_0-1573802782525.png

TomWelgemoed
12 - Quasar

I'm not sure actually.

 

What you can do is to delete the dynamic input tool and try to recreate it. You will do something similar to what you were trying to do - select an example file from your source directory, choose the sheet you're interested in, choose that you want to output the File Name only and that the first row contains data.

 

Hopefully that should be it.

Aqureshi025
8 - Asteroid

This making me crazy, done the same thing, same error of specify a sheet. However, I can see the sheet is already selected :s

Labels