Listing the list of sheet names from XLS

[okka]

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?

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

[BrandonB]

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]

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]

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.

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]

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 · Accepted solution]

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

[CharlesColeman]

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

[fmorazzini]

Hi Charles,

great solution, works perfectly

would you have the code to allow multiple input lines?

Thanks anyway

[KP_DML]

Hi @fmorazzini,

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

[JokeFun]

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'