arcpy.da.searchcursor not accepting Excel as input

[MikeMacRae]

Hello all,

I have had a script for a number of years that has always worked in python 2/ArcGIS Desktop and I have decided to bring it into the python 3/ArcGIS Pro environment. As part of the script, I use the arcpy.da.SearchCursor function to read an excel file. This has always worked in the Desktop environment, yet I am getting the following error:

with arcpy.da.SearchCursor(r"C:\MASTER.xls\'Test$'", '*') as cursor:
RuntimeError: cannot open 'C:\MASTER.xls\'Test$''

My code is:

with arcpy.da.SearchCursor(r"C:\MASTER.xls\'Test$'", '*') as cursor:
	for row in cursor:
		print(row[0])

I tested a few things to eliminate any issues:

• Moved the spreadsheet to different locations on different drives on my LAN. Same error each time
• Changed the spreadsheet from xls to xlsx. Same error.
• Set the spreadsheet as the workspace and tried iterating over the sheet. Same error.
• Changed the UNC path to a map drive. Same error.
• Tested on a feature class in a geodatabase. No issues. The code runs.
• Tested again in Desktop 10.6. No issues. The code runs.

Is there any chance that ESRI has discontinued support for the cursor on spreadsheets or is there a different approach?

[JoshuaBixby]

Remove the single quotes from around the worksheet name.

[MikeMacRae]

Unfortunately, that is not correct. The quotes are needed around the sheet name along with the $ at the end. It's needed for python to parse the path and understand that is a sheet within a excel instance. That being said, I did test and the same error occurs.

[JoshuaBixby]

On my machine with Pro, I don't have or use quotes around my sheet names and everything works fine.

>>> import arcpy
>>>
>>> # pass Excel worksheet with no quotes to search cursor
>>> with arcpy.da.SearchCursor(r"E:\tmp\sample.xlsx\Sheet1$", "*") as cur:
... for row in cur:
... print(row)
...
(10.0, 'g', 10.3, 1)
...
(22.0, 'i', -1.5, 11)
>>>
>>> # pass Excel worksheet with single quotes to search cursor
>>> with arcpy.da.SearchCursor(r"E:\tmp\sample.xlsx\'Sheet1$'", "*") as cur:
... for row in cur:
... print(row)
...
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
RuntimeError: cannot open 'E:\tmp\sample.xlsx\'Sheet1$''
>>>

[MikeMacRae]

That's interesting. That has never worked for me in either cursor on any ArcGIS Platform I've used over the years. When you navigate to the sheet using Catalog and look at the path in the address bar, it always have the single quotes around the sheet name.

Question. Which version of ArcGIS Pro are you running? We just updated to the latest version (2.7.3) python 3.7.9. I'm wondering if this is a bug introduced at the version I am running?

[JoshuaBixby]

I am in the same boat as Dan, i.e., using Pro 2.8 beta (beta 1, not beta 2).  If I get a chance to test on a 2.7.x machine, I will let you know.

[BlakeTerhune]

Ironically, I am in development of a script that does just this. I was working with ArcMap but just tested the search cursor on Excel in my ArcGIS Pro 2.7 environment and it works. No single quotes around the sheet name.

[DanPatterson]

Why not use Excel To Table and get a geodatabase table to simplify a lot of issues with excel files.

[MikeMacRae]

Thanks Dan and good suggestion. The bigger code is quite involved so am hoping there's a simple answer to the original issue, but I just might test out to see if that workaround might be worthwhile.

I'm happy to see you're still perusing this forums. You've provided some good answers and suggestions to my own posts in the past, so thanks for that.

[DanPatterson]

No problem Mike.  I know esri has worked on dealing with excel files for a long time.  Because of its nature of allowing anything in any cell.  

They have some pretty good advice in...

Work with Microsoft Excel files in ArcGIS Pro—ArcGIS Pro | Documentation

which covers a lot of issues.  I usually find it easier to avoid excel as much as possible unless I have to, but it often isn't the case, so strict formatting.  Some people prefer getting tabular data out into Pandas, but arcpy has some really handy numpy exchange functionality.

Good luck