Dynamic Input Same Tab From Multiple Files

[JeremyN]

I'd like to input the same tab from multiple files.  Seems like I should be able to use the directory tool with full path, add a tab name, then send to the dynamic input too, but I can't seem to figure out the format for specifying the tab name.  Has anyone done this?

[T_Willins]

Hi @JeremyN,

If the sheets are all in the same format, it can be done with a single input tool.  Change part of the filepath to a wildcard while leaving the Table or Query name to the correct sheet.  You can turn on Output File Name as a field if you need to differentiate between the files.

[JeremyN]

Thanks!  Actually looks like one field is getting pulled in as a different data type, so unfortunately that won't work in this situation.

[AbhilashR · Accepted solution]

Hi @JeremyN, could you give the attached solution a try to bring in all files into Alteryx? The Directory tool reads the file path, which gets passed to first macro that fetches individual excel tab names, which in-turn is passed through the second macro to read-in the data. This approach works any time your data structure changes from file-to-file.  

[JeremyN]

The top workflow works for me with a couple of tweaks (I'm using an xlsm file instead of xlsx), so I modified the first filter.  Also, I added a filter after the Sheet Names macro to only pick up the sheet I'm interested in.  Thanks!

[sabidor829]

Hi @AbhilashR,

Would you be able to attach your dummfile.xlsx into this discussion. I am trying to create my own macro for my workflow but I want to see what's exactly in that file. 

Thanks,

Stephen

[AbhilashR]

Hi @sabidor829, you can use any existing .xlsx file in place of the dummy file my solution refers to. I used the dummy one just to avoid the error msg the Input tool gives if you don't set it up the right way.

[sabidor829]

Oh I see. I can use any blank excel sheet and the dynamic input will work. Thanks @AbhilashR !