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

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

Input

Output

Accepted answers

Joe_Mako

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.

All comments

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>"

list sheet names.png

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.

dynamic input.png

ivy29gm

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.

ReadAllSheetsFromAllWorkbooks.yxzp

MikeB

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.

cqwang

@Joe_Mako

The macro tool is really helpful, thank you~!

but I encountered an issue that I have two sub-folders within a parent folder and in Directory I specifically only bring in the parent path. However, the macro only output one of the subfolder. When I delete the subfolder in parent folder and rerun the macro, it then recognize and output second one. Not sure why this happened, any ideas?

It's like the path for Parent is : C:\Users\Templates

and two sub-folders are C:\Users\Templates\A C:\Users\Templates\B

However, when I run the macro, it only output full path start with C:\Users\Templates\A

If I delete folder A from Templates folder and re-run exactly same macro, it then able to output C:\Users\Templates\B

I think it might be I have too many fields in excel file, cause one of the run the macro only picked up 2 of 3 files included in the same folder. If so, anyway to fix it?

Thank you~!

aoxomoxoa

Hello

two things from an Alteryx newbie :

first, is it possible for the Dynamic Input tool to relay the tab name, not the file name - as a field in the output file ?

I saw a solution online that mimics this, but it only does so by adding a column to each tab's dataset first, with the individual tab name shown in every cell..

far from optimal, especially if the tab data is automatically generated and you want to avoid an intermediate step/formula

second, I have set up a dynamic input tool and get the schema error everyone talks about

It seems that either Alteryx incorrectly assigns a string type to V_WString when the same field on all the other tabs reflects V_String

Initially I though there were character(s) in that one column that Alteryx just does not like

however, that seems strange since everyone seems to get the same issue with a Dynamic Input

any ideas ?

jcardoso

Hi, i was going through the Joe's macro and couldn't find where,

Question: it's possible with this macro to update the input Directory automatically, which means, make the input Directory dynamic?

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235