Dynamic Input with WildCard Sheet names?

[matula23]

Hello,

I have a set of files that I am trying to open using a combination of the directory tool and the dynamic input tool by replacing the full path. My problem is that I need to specify a sheet name in the dynamic input tool in order to open these files.

However, my sheet name is variable based on which quarter/year we are working in. There is some consistency in the beginning few letters where one file always has a tab starting with "ER" and one starts with "NON-ER" but then the remaining part of the sheet name will be Q1 - Q2 2023 or Q2 - Q3 2023 and so forth depending on time of year. Is there a way for me to include a wild card in my modified full path to pull any sheet that includes "ER" or "Non-ER" with the dynamic input? I've seen some solutions suggest to just pull the list of sheet names, but I'm confused how to do that from the directory tool. My understanding is that you need to use the input data tool to pull a list of sheet names, but my file path is variable and comes from the directory tool i have set up.

[Prometheus]

@matula23 You can replace your Directory tool with an Input Data tool and configure it to only output a list of sheet names and, under Output File Name as Field, select "Full Path." Then you can use a Formula tool after that with this expression: Replace([FileName], '<List of Sheet Names>', [Sheet Names]). You'll then have the full path that you can input to your filters as you have them and then send to your Dynamic Input tools.

 

 

 

[matula23]

.

[matula23]

This is definitely getting me on the right path, but I noticed that when I use the input tool with the wildcard *.xlsx, I am getting all the sheet names for only the first xlsx file in the folder. Is it possible to have it list all the sheet names for every xlsx file along with the file path?

[Prometheus]

@matula23 It should list every XLSX file in that folder. Make sure your Input Data tool is configured correctly. You should have a list of every XLSX file in that folder.

 

 

[matula23]

Hmmm maybe I'm missing something. I have my input file set up similar to how you have it set up, but I'm only seeing the first file in the folder with the 4 tab names for that file. The folder has 3 xlsx files though:

[matula23]

I found the issue, but I'm not sure how to resolve it... It looks like my input tool is skipping the 2 other files in the folder because they have a different schema than the first file. This makes sense because they are very different files with different columns/headers/tabs. I'm only interested in finding the sheet names and path of the files, so that I can dynamically open them. Any suggestion on how to resolve this issue?

[matula23]

I just tried to create a batch macro that takes the full path from the directory tool and tries to get the list of sheet names for each individual file one at a time. I set the output settings to finished until all iterations are complete, but I am still only getting the sheet names for the first file listed in the folder.

[caltang]

When you use Input Tool, use the List of Sheet Names first, then only change to * to call all the files in. Make sure to have "Full Path" selected as well in your configuration pane.

That way, when you click Refresh on your Input Tool, you see all the sheet names. From there, use a Formula Tool and select "Full Path" as the column, then change the path as such:

[Full Path] + "|||" + [Sheet Names]

Then, what you can do is just use a Filter Tool to get the sheet you want.

Then, create the Batch Macro with a control parameter setting that lets you choose the Full Path in your main workflow. That loads in all the data that you want, from the sheet you need, which comes from all the files you want. 

Hope this gives some idea!

[matula23]

@caltang  - As I mentioned in my above replies, this approach is not working for me, because the input tool is only pulling the first file in the folder, because the schemas between the files are different.