Dynamic Input with WildCard Sheet names?

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.

Developer

Developer Tools

Input

Dynamic Processing

Accepted answers

All comments

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.

List of Sheet Names.PNG

Replace List.PNG

Renamed FileName.PNG

Modify FileName.yxzp

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.

Input Configuration.PNG

List of Sheet Names2.PNG

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.

matula23

@caltang - As I mentioned in my previous replies, I have updated the settings in the batch macro interface designer to auto config by name, and I am still only getting the first file in the folder.

caltang

Okay - I misunderstood you there. I thought you mentioned the Macro was giving you issues whereas it was your Input tool giving you issues.

Okay, no worries - here's what you can try. Change it with a Directory Tool, and for each full path, use the Append Tool with a Text Box input of all your known Sheet Names since you mentioned they follow a specific naming convention, I'm sure you only need a few of them.

From there, use the fullpath, and change as per before only this time, change after the sheet names have been appended. This should work.

Lin

It is a version issue.

The solution mentioned by @Prometheus works in 2023 but not work in 2021.

2021 requires the sheet name to be the same.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177