topic Re: using dynamic input on excel files with different tab / worksheet names in Alteryx Designer Desktop Discussions

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

ivy29gm — Thu, 24 May 2018 01:25:34 GMT

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

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

Joe_Mako — Wed, 09 Sep 2015 20:52:04 GMT

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.

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

ivy29gm — Thu, 10 Sep 2015 00:05:23 GMT

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

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

AdamR_AYX — Thu, 10 Sep 2015 09:05:15 GMT

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.

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

ivy29gm — Fri, 11 Sep 2015 17:05:06 GMT

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

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

Joe_Mako — Wed, 09 Nov 2016 19:09:29 GMT

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.

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

s_pichaipillai — Mon, 14 Sep 2015 07:08:32 GMT

Hi There,

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

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

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

s_pichaipillai — Mon, 14 Sep 2015 07:09:22 GMT

attaching the sample workflow as well

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

AdamR_AYX — Mon, 14 Sep 2015 13:20:22 GMT

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!

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

ivy29gm — Mon, 14 Sep 2015 21:03:52 GMT

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

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

MikeB — Fri, 18 Sep 2015 19:26:20 GMT

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.

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

ivy29gm — Fri, 18 Sep 2015 19:48:33 GMT

Hi Michael,

That's great news. However, why only first? Could you add a toggle in the input tool config where the user can specify 'First' or 'All' sheets? Will it be an Actionable field?

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

MikeB — Fri, 18 Sep 2015 19:57:19 GMT

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.

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

ivy29gm — Fri, 18 Sep 2015 22:18:39 GMT

Absolutely agree. All would only work if schema is the same for all sheets. or define like the 3 different output modes in batch macro?

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

cbridges — Fri, 11 Mar 2016 21:01:05 GMT

Wowwwwwww this is amazing!

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

Mon — Tue, 15 Mar 2016 02:51:58 GMT

Thanks and it works on my end.

Do you happen to know if I want to capture the filenames of the files used in the dynamic input tool?

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

s_pichaipillai — Tue, 15 Mar 2016 02:54:48 GMT

@Mon

the below image shows the option to Grab the file name from Dynamic Input tool

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

Mon — Tue, 15 Mar 2016 03:01:51 GMT

Appreciate pichaipillai! Thank you!

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

pcatterson — Thu, 12 May 2016 16:49:27 GMT

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

(Unchecked)

FullPath

C:\Documents\File1.xls

C:\Documents\File2.xlsx

C:\Documents\File3.txt

C:\Documents\File4.yxdb

(Checked)

FullPath

C:\Documents\File1.xls|Sheet1

C:\Documents\File1.xls|Sheet2

C:\Documents\File1.xls|Sheet3

C:\Documents\File2.xlsx|Raw Data

C:\Documents\File2.xlsx|Summary

C:\Documents\File3.txt

C:\Documents\File4.yxdb

You can therefore filter the sheets you want to include and exclude and then pass through through the dynamic input tool like normal

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

MikeB — Wed, 18 May 2016 21:56:10 GMT

This may already be obvious but I figure I'll add it here in case someone reading this later is not aware of this feature.

Alteryx can pull the list of sheet names from an Excel file...

In an Input Tool, when you select an Excel files (.xlsx), you are presented with a list of sheet names from which to choose.

At the top of the list you will see this:

If you choose this, it will pull the list of sheet names from the specified Excel file.

Note that this is only available for .xlsx/.xlsm files - it does not work for .xls files or if you are using the "Legacy" .xlsx format.