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

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
17 - Castor
17 - Castor

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.



Bulien

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
17 - Castor
17 - Castor

Hi @kgordish ,

 

no problem.

You need to change your formula to be:

 

[Fullpath]+"|||Sales$"

 

That should do it.

 

M.



Bulien

kgordish
8 - Asteroid

Awesome!

 

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

Thank you. 

mceleavey
17 - Castor
17 - Castor

No problem at all, glad I could help!

 

M.



Bulien

mceleavey
17 - Castor
17 - Castor

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



Bulien

jerewatson
5 - Atom

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

mceleavey
17 - Castor
17 - Castor

No Problem @jerewatson ,

 

glad I could help.

 

M.



Bulien

Sneha_Lonkar
6 - Meteoroid

Hi,

 

 

 

Labels