Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Listing the list of sheet names from XLS

okka
7 - 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?

11 REPLIES 11
BrandonB
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. 

BrandonB
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.

KP_DML
8 - Asteroid

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)

okka
7 - 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?

KP_DML
8 - Asteroid

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

CharlesColeman
5 - Atom

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

fmorazzini
5 - Atom

Hi Charles,

great solution, works perfectly

would you have the code to allow multiple input lines?

Thanks anyway

 

KP_DML
8 - Asteroid

Hi @fmorazzini,

Attached is a variation of the workflow that supports multiple (or just one) Excel files.

 

 

JokeFun
8 - Asteroid

What does this indicate?

Error: Python (2): ---------------------------------------------------------------------------
CalledProcessError Traceback (most recent call last)
<ipython-input-1-c63f9486f906> in <module>
2 # script here (only missing packages will be installed)
3 from ayx import Package
----> 4 Package.installPackages('xlrd')
c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Package.py in installPackages(package, install_type, debug)
200 print(pip_install_result["msg"])
201 if not pip_install_result["success"]:
--> 202 raise pip_install_result["err"]
c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Utils.py in runSubprocess(args_list, debug)
118
119 try:
--> 120 result = subprocess.check_output(args_list, stderr=subprocess.STDOUT)
121 if debug:
122 print("[Subprocess success!]")
c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in check_output(timeout, *popenargs, **kwargs)
354
355 return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
--> 356 **kwargs).stdout
357
358
c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in run(input, timeout, check, *popenargs, **kwargs)
436 if check and retcode:
437 raise CalledProcessError(retcode, process.args,
--> 438 output=stdout, stderr=stderr)
439 return CompletedProcess(process.args, retcode, stdout, stderr)
440
CalledProcessError: Command '['c:\\program files\\alteryx\\bin\\miniconda3\\envs\\jupytertool_venv\\python.exe', '-m', 'pip', 'install', 'xlrd']' returned non-zero exit status 1.

 

 

Error: Python (2): ---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
<ipython-input-2-4fca0f91e045> in <module>
1 from ayx import Alteryx
----> 2 import xlrd
3 import pandas as pd
4
5
ModuleNotFoundError: No module named 'xlrd'

 

 

Labels