Pulling in one tab from multiple files

Question

Hello,

I am working through an Alteryx where I have 12 files/inputs and need to pull in one tab from each. The tab in each file is named differently (ie January, February, March, etc) but the format of the tab is exactly the same in all 12 files. My goal is to pull in all twelve tabs and have one output with all 12 tabs of data in it. I really want to avoid having 12 inputs if possible. Any help would be greatly appreciated! Thank you very much in advance.

Kandace

abacon · Answer

Makes sense. What you can do is use the underlying macros - GetSheetNames and OutputData, from the Dynamic XLSX macro. GetSheetNames gets all the sheet names out of the files in the directory, then you will need to filter each file to the sheet you want, create your filepath with those values then pass into the OutputData Macro.

The filtering to specific sheets might be tricky if you don't have a specific criteria they will each follow - filename matches the sheetname you are wanting, but these macros will do the trick once you get the desired sheets.

Hope this helps!

Bacon

kkaplan1 · Answer

Hi! Thanks for the quick reply. The files have many tabs and we only need to pull one of them in for each. I will take a look at the Macro you sent and see if I can figure out how to incorporate it.

Thank you!

abacon · Answer

@kkaplan1 Do the 12 files have multiple tabs or just 1?

If they each have 1, try this macro on the gallery.

If not, you will need to use a macro to pull the sheet names from each file, then have a filter select the right sheet. Once you do this, you can do another macro to get the data out or use the Dynamic Input tool since they are the same schema.

The macro below has macros within that pull sheet names out and data out of the files so you can use it as a guide if you have to do option 2.

https://community.alteryx.com/t5/Community-Gallery/Dynamic-XLSX-Input-Tool/ta-p/1393832

Bacon