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

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

[simon]

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?

[MikeB]

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.

[simon]

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?

[cbridges]

Wowwwwwww this is amazing!

[Mon]

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?

[s_pichaipillai]

@Mon

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

[Mon]

Appreciate pichaipillai! Thank you! 

[pcatterson]

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

[MikeB]

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:

            <List of Sheet Names>

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.