How to input an Excel sheet into Python Script tool parameter

03-29-2018 07:52 AM
Esri Notable Contributor

I am looking for a way to select a sheet within an Excel file as input to a Python Script tool in ArcGIS Pro. In the script, I am using GetParameterAsText. For the tool parameters I am using the File Data Type with the "xls;xlsx" extensions in the File Filter. 

The input goes into the GetCount_management tool and then does a few other processes. The process fails with the first tool and gives the following error:

ERROR 000840: The value is not a Table View.

When you open the Make Table View geoprocessing tool and browse to the Input Table, it allows you to access the sheets. I am looking for this same functionality within a script tool. Thanks!

0 Kudos
4 Replies
MVP Legendary Contributor

Timothy, why don't you use Excel to Table .... you can access a sheet and a table view is much easier to work with later.

MVP Esteemed Contributor

..and something to always try, do it manually then copy the python snippet from the Results window to make sure you get the syntax correct.

0 Kudos
Esri Esteemed Contributor

To enable the option to select sheets in the toolbox, you will have to use xlrd 1.1.0 : Python Package Index in the validation class of the tool.  have done this in the past and it works "without problems", but before you are going this path, be aware with the most evil of all in software development... the user! Enabling them to use Excel to select a "table" as input data is providing them with the most powerful tool to make your life as software developer almost impossible. You may be lucky and have end-users that provide data well structured, but you will have to include numerous checks to see if the Excel data selected in your tool is valid to process in the rest of the code. 

Esri Notable Contributor

Thanks to bharold-esristaff‌ for an additional tip! I can use the Table Data Type and it will let me browse down to the sheet. I just have to trade the ability to filter the input to Excel files.