Here's my dilemma. I have 200 excel spreadsheets but they all have different/unique worksheet names (so not all Sheet1). I would like to create one output from all 200 sheets.
I used the directory tool to get fullpath which feeds into the dynmaic input tool. The DI tool works great but ONLY when worksheet name or tab is the same in all files (sheet1). Same is true when just using input tool and doing *.xlsx.
How can I get input / dynamic input tool to work when all excel files contain different worksheet names (LA, NY, ATL, etc). Has anyone found a workaround for this? (I am not about to drag 200 sheets to my canvas and merge them since this will become an app.)
Just jumping in with news of a new feature coming in the next release (after 10.0).
Going forward, if you specify an .xlsx file with no sheet name, we will automatically open the first sheet (or only sheet if the file only has one).
How do you know which sheet will be "first"? Very good question. My understanding right now is that it'll be the last sheet modified, but I'm not 100% sure about this. I know that in the internal XML, the list of sheets doesn't appear to be in any particular order. So, what we can say is that you'll get the first sheet as written by Excel in the internal XML file, but beyond that we cannot guarantee which sheet you'll get in a multi-sheet file.
If your files all have one sheet each, then this might work well for you.
The request we added this for was a person who said "I have an Excel file with only one sheet in it, so why do I have to specify a sheet name?"
We thought that was a good idea and went one further by supporting files with more than one sheet as well (although it's not guaranteed which sheet you'll get in that case). So, there's really no option here, just if you don't tell us which sheet to read, we'll read the first one. Also, this is only for read and does not work when writing or appending.
Your idea of reading "all" sheets is interesting. How would you expect that to work if the sheets have different numbers of columns and conflicting "field names" and/or datatypes?
I'd love to hear your thoughts if you'd like to elaborate.
Maybe it would be more straight forward not to change the Input Tool at all. Instead, the Directory Tool could be changed. You could add a checkbox which would give you the option to show you all the different sheets within an excel file each as its own line in the directory
You can therefore filter the sheets you want to include and exclude and then pass through through the dynamic input tool like normal