cancel
Showing results for 
Search instead for 
Did you mean: 

Importing xls and xlsx with different tab names

SOLVED
Highlighted

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! 

 

 

  • Input
  • Macros
  • Preparation
mborriero
Fireball

@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

Capture2.JPG

 

 

 

Let me know if it works.

suli
Asteroid

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.

 

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'

 

 

1.PNG

 

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

 

2.PNG

 

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.

 

3.PNG

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

suli
Asteroid

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.

 

Hey,

 

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.

suli
Asteroid

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