Dynamic Input Tool (Import Multiple Excel Files and only specific Sheets)

[kgordish]

Hello,
Have some Alteryx experience, but still an acolyte.

Much to my chagrin I am struggling with the combination of the "directory tool" and "dynamic input". Searched forums. Learned how to import multiple files if in same directory (learned that), but I am trying to implement an import for hundreds of Excel files located in multiple folders. 

Overall actions so far:

Directory:

Specified a parent folder 
(Excel files of either xlsx or xlsm are present), Thus can not use wildcard for only one file type. 

Dynamic Input
Select a file in one of the folders and selected the "sales" sheet.

Browse:

Added Browse to end workflow

Getting an error.

Can someone help point out where I am screwing up?

Thought this tool combo would be easier. If I figure out my error, then I can carry over into my functional model. 

Thank you for any assistance. 

Kevin 

[mceleavey]

Hi @kgordish ,

You need to create, using a Formula tool, the fullpath using [fullpath]+"|||"+<sheet name>+"$". Then use the fullpath with the dynamic input tool.

If you need the sheet to be determined dynamically, I've attached a tool I built that gathers all sheet names from within the workbook for you.

Hope this helps,

M.

[kgordish]

@mceleavey 

Appreciate the assistance. Wish more people at my work knew Alteryx.

Hopefully getting closer. Received a variable error.
Any additional advice to push me to the solved side?

KG

[mceleavey · Accepted solution]

Hi @kgordish ,

no problem.

You need to change your formula to be:

[Fullpath]+"|||Sales$"

That should do it.

M.

[kgordish]

Awesome!

Your instruction fixed my errors and taught me a very cool feature. 

Thank you. 

[mceleavey]

No problem at all, glad I could help!

M.

[mceleavey]

...and just because I can, I've built a workflow (attached) that will dynamically determine all sheet names and all filenames and automatically import all simultaneously.

Enjoy.

M.

[jerewatson]

This was exactly what I was looking for. Thank you so much for the extra effort!!!!

[mceleavey]

No Problem @jerewatson ,

glad I could help.

M.

[Sneha_Lonkar]

Hi,

I am getting some error while using the attached file to import multiple sheet names from multiple excel files. Can you please help?

My query is- I have multiple excel sheets in various sub-folders and have variable sheet names. I tried reading those excel sheets using Directory, filter (to select files which i require) and Dynamic input tool. But the sheets names are not the same in each file and hence unable to pick the files.

Thanks,

Sneha