Can I escape field names when building a Search Cursor?

Question

I am trying to iterate through an excel spreadsheet using arcpy but having trouble querying fields that contain spaces in their names:

If I start by examining the fields by the python console I see this:

>>> source_path = r"\path\to\spreadsheet.xlsx\'sheet_name$'"
>>> fields = arcpy.ListFields(source_path)
>>> fields[1].name
u'Shire'
>>> fields[8].name
u'Area ha'

Then if I build a da.SearchCursor like this I am able to query "Shire" successfully:

>>> with arcpy.da.SearchCursor(source_path, fields[1].name) as cursor:
...     for row in cursor:
...         do_some_work(row)

But if I apply the approach to query "Area ha" it is unsuccessful:

>>> with arcpy.da.SearchCursor(source_path, fields[8].name) as cursor:
...     for row in cursor:
...         do_some_work(row)
Runtime error 
Traceback (most recent call last):
  File "<string>", line 2, in <module>
RuntimeError: Syntax error (missing operator) in query expression 'Area ha'.

This leads me to ask how to escape the space character in this context or otherwise achieve this query?

I am running:

• ArcGIS Desktop 10.3.1.4959
• Microsoft Office Access database engine 2007 12.0.1418.1031

Answer 1

I think I will share my experience in Excel=>ArcGIS data transfer. I do it through 2 small pieces of code that saved me tons of time and nerves.

First is Excel macro stored in personal.xls (or similar for latest Excel versions) book, thus visible to any active Excel book:

Sub toARCGIS()
' toARCGIS Macro
' Keyboard Shortcut: Ctrl+Shift+E
    Kill "C:\FELIX_DATA\toGIS.xls"
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\FELIX_DATA"
    ActiveWorkbook.SaveAs Filename:="C:\FELIX_DATA\toGIS.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWorkbook.Close
End Sub

It takes selection from any active book (no formulae!) and saves it to new (fixed) book, in my case it is C:\FELIX_DATA\toGIS.xls

I also have a tiny script, that is attached to the menu button in my mxd, which is the same mxd I keep for geoprocessing.

# Imports Excel table to one inside working database
import arcpy, traceback, os, sys, time
from arcpy import env
env.overwriteOutput = True
destTable=arcpy.GetParameterAsText(0)
def showPyMessage():
    arcpy.AddMessage(str(time.ctime()) + " - " + message)
try:
    arcpy.ExcelToTable_conversion(r"C:\FELIX_DATA\toGIS.xls",
                                  destTable, "Sheet1")
except:
    message = "\n*** PYTHON ERRORS *** "; showPyMessage()
    message = "Python Traceback Info: " + traceback.format_tb(sys.exc_info()[2])[0]; showPyMessage()
    message = "Python Error Info: " +  str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"; showPyMessage()

It has single input parameter that is the name of a table to store imported data from Excel. It copies table from mentioned fixed toGIS.xls.

This is the look of imported table in ArcGIS:

Great news that second column is being treated as TEXT, despite first value being numeric(!). And more importantly ArcGIS uses Excel headings as aliases, but actual field names are very legit. In this case spaces are replaced by underscore symbol:

Answer 2

I would consider converting the Excel sheet into an in_memory table to handle the illegal characters and whitespaces first. The da.SearchCursor won't let you specify the fields with a whitespace.

However, if this is not something you would like to do, I would use the * to get all the fields from Excel and then construct an own data structure to work with. This would give you a way to work with the rows using the source Excel field names; however, you can choose yourself which fields you are interested in as you access the OrderedDict by key.

Source Excel:

from collections import OrderedDict
import arcpy

data = [f for f in arcpy.da.SearchCursor(r"C:\GIS\Temp\Whitespace.xlsx\Sheet1$", "*")]
fields = arcpy.ListFields("C:\GIS\Temp\Whitespace.xlsx\Sheet1$")

rows = [OrderedDict(zip(([f.name for f in fields]), row)) for row in data]
print rows[0].keys()

for row in rows:
    print row.values()

for row in rows:    
    print row['Name'], row['Area ha']

Output:

[u'Name', u'Shire', u'Area ha']
[u'a', 11.0, 400.0]
[u'b', 22.0, 500.0]
[u'c', 33.0, 600.0]
a 400.0
b 500.0
c 600.0

Answer 3

The source of your problem is that Excel allows anything to be typed in and given the opportunity most people will spend hours beautifying their spreadsheets so they look nice in Excel but in the process make it unusable for a GIS. Or some other bit of software exports into a wacky format.

As the above comments indicate, you have illegal field names and by far the easiest way to resolve this is fix it in the Excel document first.

You should read this blog page as this is a problem that will not go away!

https://blogs.esri.com/esri/arcgis/2015/10/06/formatting-an-excel-table-for-use-in-arcgis/