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,

Please see the solution attached. You can do that with a directory tool - just make sure you configure it inside the template (see the part highlighted in the image).

Regards,

Tom

[Aqureshi025]

sHEET SPECIFICATION ERROR

[Aqureshi025]

I am getting an error

No SHEET SPECIFIED

[TomWelgemoed]

Hi @Aqureshi025 ,

When you edit the template file, you need to specify the sheet location - it looks like the image below.

Steps:

1. Click on Edit below the Input Data Source Template

2. Pop-up comes up like in the image below. Here you need to specify one of your example files that you are trying to import. You can use the ones I gave you or a few of the 200 you're trying to import

3. Make sure the sheet is specified - I named it like you referenced previously. You can do this by selecting the elipses next to the Table or Query statement (line 3 in the dialog box)

4. OK that and you should be good to go.

By the way, make sure the first tool, the directory tool, is reading from the directory you want it to read from. It's only function (if you're not familiar) is to read all the files of the type you specify in a given directory. So make sure those files are present in the directory and that the right search string is used (e.g. *.xlsx). 

Hope this helps.

[Aqureshi025]

These are my files.

Can you run those? thru your code

my code is there, but it is all manual

[Aqureshi025]

Still Same error

[TomWelgemoed]

Hi,

I'm not sure I understand what you're doing - can you send me an example of how your workflow looks now? I have all your files, so you can just share your workflow and how you're trying to run it?

Hard to give you guidance otherwise. Or at least explain your steps?

[Aqureshi025]

In the attached zip file, I have uploaded my workflow as well. 

12 Input tool, then join and then output.

I want to avoid the 12 times input tool as I have 200+ files.

My 12 files is at Desktop in a folder

[TomWelgemoed]

@Aqureshi025 ,

I think the issue is that you're not following the approach I've given you - your example was an entirely different method.

Nevertheless, I'm attaching an example that uses the very files you gave me. I hope this is not sensitive data by the way - if it is, tell me so I can delete it.

The only things you should have to change now is the directory (the 1st tool) where this is reading from - and it should work.