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?

[JoeBorgione]

 ...I usually find it easier to avoid excel as much as possible...

^^^^ THIS!^^^^^

[DanPatterson]

Works in Pro 2.8 Beta 2

import arcpy

f = r"c:\Temp\Test28.xlsx\Test28$"

with arcpy.da.SearchCursor(f, '*') as cursor:
	for row in cursor:
		print(row[0])
        
b
c

[MikeMacRae]

Hello everyone,

I believe I solved my issue. After taking some of your suggestions to remove the single quotes around the sheet name, I tried something else. In my case, my actual sheet name had 3 words in it, separated by spaces (something like: my sheet name$). I decided to test to see if the spaces were causing the issues and it appears that it did. I replaced the spaces with underscore, removed the single quotes and my final path looked something like:

r"C:\MASTER.xls\My_Sheet_Name$"

and that worked. As mentioned, I had no issue using my previous path (with white spaces and single quotes) in any of Desktop 10.x, so this appears to be introduced somewhere with Pro.

Hope that helps someone else having the same issue.

[DanPatterson]

That is because the Excel to Table tool does it for you

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

For example, a sheet called Year to Date in Excel will display in the Catalog or Contents pane as 'Year to Date$', placing the name in quotes since it contains spaces. When used in a geoprocessing tool, the underlying table is used directly, so you may notice a slight change in the name. Using the same example, if you drag the Excel sheet Year to Date into a geoprocessing tool, or select it from an input drop-down menu, it will be represented as T_Year_to_Date$_.

Spaces are basically not recommended for paths, names , basically anything. 😉