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.)
Great Joe. This seems to work great for one excel file with multiple sheets but what if you have multiple excel files that have different sheet names? So I need sheet list of all 200 excel files and feed that to dynamic input.
Thanks for the module. It works really well but only when the sheet names have the same lenght (e.g. Sheet1 = 6).
So I created a third worksheet (attached) which resembles my situation. I changed the name of SheetB to SheetBB and it gives me this error:
Error: Dynamic Input (2): The file "D:\Alteryx\Community\ReadAllSheetsFromAllWorkbooks\Worksheet3.xlsx|<List of Sheet Names>" has a different schema than the 1st file in the set.
In my case, this error is somewhat misleading because the schema/fields are all the same in all 209 spreadsheets but not the sheet name length. When I run your module on my files, only 6 files are successfully processed with the following sheet names (DMA names with length 18):
Wildcard XLSX Input - Description - "Reads in multiple sheets from multiple Excel files from a directory. Optionally can filter out sheets, can pull field names from a header row, can auto configure field types, can sample N random sheets, and if the headers do not match then raw data for the sheets that did not match will be in the 'N' output."
Attached are two Macros in a package, one is nested inside the other, that you can place in your "My Macros" folder, and use the one called "Dynamic XLSX".
Here is an example of it in use:
As you can see I added a few features, like picking what row the header is on (all rows prior will be dropped), if you want Auto Field Type detection, and if you would like any sheets excluded.
If you select "Field names in data", but the field names are not the same, it will continue to work with warning, but use F1,F2,etc for field names instead. I could write some more logic to handle and message on, like having another output for data that did not match the common format, in future versions.
This is just a first draft, please private message me if you would like any other features added, or if something does not work right.
Using your latest version, I was able to parse all my spreadsheets successfully so that's awesome! Great solution using batch macro twice. I just need to do some REGEX cleanup afterwards and I'll be good to go. I think Alteryx should put this in their sample marcro's by default.