Oh no! The Community appears to be haunted! Help us find all the spooky specters here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

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

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

kgordish
8 - Asteroid

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.

Sheet Error.JPG

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 

12 REPLIES 12
mceleavey
16 - Nebula

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
8 - Asteroid

@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

 

Variable.JPG

mceleavey
16 - Nebula

Hi @kgordish ,

 

no problem.

You need to change your formula to be:

 

[Fullpath]+"|||Sales$"

 

That should do it.

 

M.

kgordish
8 - Asteroid

Awesome!

 

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

Thank you. 

mceleavey
16 - Nebula

No problem at all, glad I could help!

 

M.

mceleavey
16 - Nebula

...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
5 - Atom

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

mceleavey
16 - Nebula

No Problem @jerewatson ,

 

glad I could help.

 

M.

Sneha_Lonkar
6 - Meteoroid

Hi,

 

 

 

Labels