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-All-Excel/ba-p/5558
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?
Attached is a workflow using the Python method if you want to give it a try.
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.
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.
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 PackagePackage.installPackages('xlrd')
from ayx import Alteryximport xlrdimport pandas as pd
# Read in the first cellinputfile = Alteryx.read("#1").iloc[0][0]
# Open Excel filewb = xlrd.open_workbook(filename=inputfile)
# Get the sheet names and send outAlteryx.write(pd.DataFrame(wb.sheet_names(), columns=['TabName']),1)
Nice! Thanks for sharing. Once resolving the xlrd package install it was off to the races! Thanks again.
