Importing xls and xlsx with different tab names

zhenming91

Hello Alteryx community,

I have a folder of xls and xlsx spreadsheet files, each with different tab names. I am only interested in importing the first tab of the spreadsheet (so perhaps a formula would work here), how do I go about doing this? 

For example:

a.xlsx have a first tab named 'x' 

b.xlsx have a first tab named 'y' 

c.xls have a first tab named 'z'

d.xls have a first tab named 'w'

I figured that using directory, followed by a macro (control parameter, update value with formula, input data) would work here. But it seems that the table or query in input data requires me to specify the tab name for the template, and this workflow would not work when there are differing tab names. 

Thank you! 

suli

Hi,

I think the biggest challenge here is to read 'List of sheet names' from XLS file since this option is available only for XLSX.

You can extract  'Sheet finder macro' from this solution:

Macro:

https://gallery.alteryx.com/#!app/Read_All_Excel_Files/58dd51b3a18e9e18fca64172

Reference:

https://community.alteryx.com/t5/Engine-Works-Blog/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-Al...

Once you have read all sheet names from all files you can use 'Tile' tool and filter out first sheet for every file.

Sample solution attached.

Sheet Names.yxzp