Input Excel Spearsheet with variable tab name

[EveM]

Hello everyone,

I have an Excel Spreadsheet input that has a variable tab name.

For example: the tab name is "Orange_1234" for now, but in a month, it will be "Orange_5678". The "Orange" part is there to stay but the rest changes everytime I get a new spreadsheet.

Is there a way to tell the input tool to choose the first tab according to the first word its name contains? Or to simply make it choose the first sheet without specifying a name? Preferably without using the dynamic input tool.

Thanks in advance!

Eve

[Thableaus]

Hi @EveM 

With the Input Tool you could import the Sheet Names and use the Sample Tool to get the first Sheet, feeding then a Dynamic Input Tool.

That would be an option, but since you don't want to use the Dynamic Input Tool, I'm not sure if it could be helpful to you.

Cheers,

[joshuaburkhow]

Hi @EveM 

Is there a reason you wouldn't want to use the dynamic input tool? This is a perfect use case for using this. I would bring in the list of tabs and use a filter to pick the one you want (ie begins with "Orange") and then run that tab through the dynamic input tool. 

It would be really nice to use wildcards in the sheet name of an excel file like this: 

I submitted the idea so if you want this you should star it here: 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Add-the-ability-to-use-wildcards-on-sheet-na...

[EveM]

@joshuaburkhow Could you show me the workflow I would need to do to use the dynamic tool? Everytime I try to use it it doesn't work. Thanks!!!

[EveM]

@joshuaburkhow Could you show me the workflow I would need to do to use the dynamic tool? Everytime I try to use it it doesn't work. Thanks!!!

[neilgallen · Accepted solution]

I got ya, @joshuaburkhow.

The trick here is once you use the input tool to get a list of sheet names, you then need to modify the file path so the dynamic input tool knows where to look. That's easily done with a formula tool. Just make sure your input has the "Output file name as field" option set to "full path"

Once connected to the dynamic input tool, make sure you have the 'action' set as "change entire file path"

[EveM]

@neilgallenthank you so much you just saved my life!!!

[joshuaburkhow]

@neilgallen is a rockstar! (Thanks bruda! 😉 )

@EveM you should also know that you can not only use that to read in different sheets in an excel file but also any amount of files in a directory (or sub-directory) like the example I added!

[laceyaidan]

Thanks - this was really helpful. I used this to ingest and union a bunch (282) tabs that are similarly named and have the exact same format/structure. I'd like to add a column with the sheet name. Any ideas how to do this?

[AbhilashR]

@laceyaidan - referring to the image below, in the Input or Dynamic Input tools, modify the 'Output File Name as F...' option to include Full Path. This instructs Alteryx to provide the path of the file being read, alongwith its tab name.