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

Alteryx designer Discussions

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

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Listing the list of sheet names from XLS

Meteor

Hello,

 

I'd like to know how to list the sheet names from a XLS file.

I can't convert the file into XLSX, so I'm trying to find a solution to list the sheet names from an XLS file.

I've found the following macro: https://community.alteryx.com/t5/Engine-Works-Blog/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-Al...

 

But I really don't understand the macro "SheetFinder" itself:

Do I need to change anything in the following Run Command tool?

Capture.PNGCapture2.PNG

It looks a bit sophisticated for simply reading a single XLS file... Could you help me out on this?

Alteryx
Alteryx

This is because xls files are legacy at this point and use old Microsoft drivers. The method for determining the tabs from these files is found in the supporting files folder using the VBScript file ExcelTabsScript.vbs in the run tool. 

Alteryx
Alteryx

If you just use the "Read All Excel Files" Macro and don't change anything in it, it actually includes the FileName which has the sheet names as well. To keep things simple it might be easiest to just use the macro and do a summarize on the FileName field in order to get the list of paths and sheet names.

Meteor

Another option would be to use Python with the xlrd library. The path to the Excel file can be supplied from an incoming tool (shown in example) or hard-coded in the Python code. The code could also be modified to take in/process multiple files.

 

PythonExcelWorksheets.png

 

Python Code:

from ayx import Package
Package.installPackages('xlrd')

 

from ayx import Alteryx
import xlrd
import pandas as pd


# Read in the first cell
inputfile = Alteryx.read("#1").iloc[0][0]

 

# Open Excel file
wb = xlrd.open_workbook(filename=inputfile)

 

# Get the sheet names and send out
Alteryx.write(pd.DataFrame(wb.sheet_names(), columns=['TabName']),1)

Meteor

I wish I could use it but I keep on having the following error

Error: Sheet Finder (2): Record #1: Tool #33: The external program "cscript.exe" returned an error code: 1

What should I do?

Meteor

Attached is a workflow using the Python method if you want to give it a try.

Highlighted
Alteryx Partner

@KP_DML That Python workflow is fantastic.  It solves the exact problem I was having.  Thanks!

Labels