This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hello. Apologies if my question is not completely clear as it is my first time posting here, and thank you in advance for the help.
I am trying to solve the following problem at the moment:
1. I have a number of .xlsx files in a single directory on my computer
2. Each file has multiple (static) named ranges spread across various sheets
3. I need to be able to loop through each named range in each file and pull through the data in order to convert it into a specific format (final goal is to create a normalised dataset built from the data in these named ranges for Tableau to ingest).
What I have tried so far:
I have tried to replicate this example that uses a batch macro to read in multiple sheets:
In the above, instead of feeding the dynamic input tool with sheet names, I feed it with names of the named ranges (the names, not the Excel ranges) - it works for the first named range and stops after that which seems to indicate that the Action tool is not doing its job.
Does anyone have thoughts or experience with this? Is there a way to import all named ranges in the same way that one can import all sheets from an Excel file?
Any thoughts would be appreciated. Thank you very much!
Are the named ranges the same in every spreadsheet? If so, you could try something like the following:
1. Use Directory tool to pull in the file names from your specified directory.
2. Use a Text Input tool to list the Named Ranges that you want to pull in from each file.
3. Use an Append tool to add each of those Names Ranges to each file record from your Directory tool.
4. Use a Formula Tool to add "|||[NamedRange]to each of the full file names, where [NamedRange] is each of the named ranges from your Text Input tool.
5. Run this concatenated list of file names + named range name through a Dynamic Input tool and/or a Batch Macro (if the schema could be different between files, you'll need to do macro route)
I've attached an example, which includes a second option that might work that shows how you can use a wildcard * to pull in multiple files from the same directory for the Dynamic Input template file, then use a list of Named Ranges to update just the table name... it will essentially do the same thing as the steps above.
If your schema is different in each file, you might need to use the Batch Macro example in the link you posted, as this will help get around the schema differences... hopefully this points you in the right direction!
Thank you very much.. this is helpful. I've been trying something similar with a combination of a batch macro and dynamic input. To your point, I am not pretty sure that the issue is with using the dynamic input, because the schemas vary across the different named ranges. I will try a couple of things with a batch macro and post here as to whether it works.