nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Find more posts tagged with

Accepted answers

All comments

danretired

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. 😉

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.

danretired

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

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?

JoeBorgione

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

^^^^ THIS!^^^^^

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$''
>>>

danretired

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

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.

danretired

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

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

Remove the single quotes from around the worksheet name.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.