topic Re: arcpy.da.searchcursor not accepting Excel as input in Python Questions

arcpy.da.searchcursor not accepting Excel as input

MikeMacRae — Fri, 30 Apr 2021 01:53:35 GMT

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?

Re: arcpy.da.searchcursor not accepting Excel as input

JoshuaBixby — Fri, 30 Apr 2021 01:40:20 GMT

Remove the single quotes from around the worksheet name.

Re: arcpy.da.searchcursor not accepting Excel as input

MikeMacRae — Fri, 30 Apr 2021 01:44:29 GMT

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.

Re: arcpy.da.searchcursor not accepting Excel as input

DanPatterson — Fri, 30 Apr 2021 01:47:06 GMT

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

Re: arcpy.da.searchcursor not accepting Excel as input

MikeMacRae — Fri, 30 Apr 2021 01:51:33 GMT

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.

Re: arcpy.da.searchcursor not accepting Excel as input

DanPatterson — Fri, 30 Apr 2021 02:06:08 GMT

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

Re: arcpy.da.searchcursor not accepting Excel as input

JoshuaBixby — Fri, 30 Apr 2021 15:18:44 GMT

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

Re: arcpy.da.searchcursor not accepting Excel as input

JoeBorgione — Fri, 30 Apr 2021 16:01:07 GMT

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

^^^^ THIS!^^^^^

Re: arcpy.da.searchcursor not accepting Excel as input

MikeMacRae — Fri, 30 Apr 2021 21:30:53 GMT

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?

Re: arcpy.da.searchcursor not accepting Excel as input

DanPatterson — Fri, 30 Apr 2021 21:47:19 GMT

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

Re: arcpy.da.searchcursor not accepting Excel as input

JoshuaBixby — Mon, 03 May 2021 14:31:24 GMT

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.

Re: arcpy.da.searchcursor not accepting Excel as input

BlakeTerhune — Mon, 03 May 2021 15:31:07 GMT

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.

Re: arcpy.da.searchcursor not accepting Excel as input

MikeMacRae — Mon, 03 May 2021 22:25:00 GMT

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.

Re: arcpy.da.searchcursor not accepting Excel as input

DanPatterson — Mon, 03 May 2021 22:50:27 GMT

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