Showing results for 
Search instead for 
Did you mean: 

Importing xls and xlsx with different tab names


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! 




@zhenming91you can try something like this.


In the first input tool be sure you select all your excel using "*" and you select the option "output file name as field: full path"Capture.JPG




and use those options in the dynamic input





Let me know if it works.




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:






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.



I tested your sample solution workflow by changing the directory path to a folder containing one xlsx and xls spreadsheet, each with two different tab names. I got the following error ' The external program 'csript.exe' returned an error code :1'





Strangely enough, when I ran the workflow inside the macro, I got some results.




Using a dynamic input on the output of the formula tool doesn't work. Little bit more background : My goal is to do a batch macro (some workflow that I have created) on each of the filenames in turn. I already a batch macro that could read all xlsx files in a folder and perform the same workflow for each xlsx file. However, now with a bigger data set, I need to also run the workflow on xls files.




Hey, thanks for your solution there, but it does not solve the issue of needing to import both xlsx and xls files...


I see.


Did you save this workflow somewhere in the documents or opened it directly when downloading from here? If could happen it is running in temp folder, and you have no right to write there.


In the sheet finder setup I also checked the box to use XLS finder for all types.


I run thin macro with a given setup and it read both - XLS and XLSX files, giving me a list you can later work with.





I tried that but the issue persist. But at least the above proposal with the macro that you introduced works, it successfully export all xls and xlsx files into a single data table, so I would mark it as solved.


If the macro inside the workflow works well, you can simply save it as a stand-alone macro and re-use.