community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Alteryx
Alteryx

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.

Bolide

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?

 

Alteryx
Alteryx

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.

Bolide

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?

Bolide

Wowwwwwww this is amazing!

@thizviz
Atom

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?

 

@Mon

 

the below image shows the option to Grab the file name from Dynamic Input toolDYip.png

Atom

Appreciate pichaipillai

ACE Emeritus
ACE Emeritus

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

Alteryx
Alteryx

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.

Labels