using dynamic input on excel files with different tab / worksheet names

[simon]

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.)

Thanks,

Simon

[Joe_Mako]

In v10 when using an Input Data tool to connect to an Excel file, there is an option for "<List of Sheet Names>"

That you can use in conjunction with a Dynamic Input tool to union all the sheets in the Excel file, this tool does require the same data structure for each sheet.

[simon]

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,

Simon

[AdamR_AYX]

Hi Simon

For multiple files you will need two dynamic inputs.  Like this

The first gets all of the sheet names in all of your workbooks.  The second gets all of the data from all of the sheets.

I've attached the module with the details.

[simon]

Hi Adam,

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):

(Portland-Auburn ME)
(Charlottesville VA)
(Dallas-Ft.Worth TX)
(Des Moines-Ames IA)
(Biloxi-Gulfport MS)
(Casper-Riverton WY)

The other files have a different sheet name length and therefore are not processed. 

Do you think we can fix the length issue? Then we have this resolved. (Note, unfortunately I cannot change the sheet name because it's system generated)

Cheers,

Simon

[Joe_Mako · Accepted solution]

EDIT: ​The originally attached macro was less than ideal, you can find an improved macro for reading in all sheets from a directory of .xlsx files in the CReW macro pack at http://www.chaosreignswithin.com/p/macros.html

​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.

[s_pichaipillai]

Hi There,

Here is the another one i used for looping over the excel sheets in a single file

1. Created a Macro to Grab the Excel sheet names
2. Use Text Input tool to specify the path. something like below image
3. 
4. Then use Dynamic Input Tool for looping all the excel sheets (Remember to configure the Meta data of one excel sheet in DI tool)
5. 
6. the final workflow look like below
7. 
8. PFA files for your testing

[s_pichaipillai]

attaching the sample workflow as well

[AdamR_AYX]

Hi Simon,

So that looks like a limitation that I hadn't thought of.  Sorry about that.

However Joe's macro looks excellent and is a more contained solution too!

[simon]

Hi Joe,

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.

Thanks!

Simon