4

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
  • Is it possible to add underscore _ in the space area in Excel and check if this can solve the problem? – ahmadhanb Feb 27 '18 at 7:54
  • 5
    I do excel to table first, it will fix illegal field names. – FelixIP Feb 27 '18 at 9:04
  • @ahmadhanb replacing spaces with underscores in the spreadsheet is effective, but they are being produced by a db export process (with field names decided upstream from there). Ideally I would like a workflow to consume them as is. – Kelly Thomas Feb 27 '18 at 9:26
  • A workaround is to specify all fields with '*' in the cursor. – BERA Feb 27 '18 at 9:57
  • @FelixIP That sounds like the basis for an answer if you want to write it up. – Kelly Thomas Feb 28 '18 at 1:59
3

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:

enter image description here

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:

enter image description here

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:

enter image description here

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
1

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/

  • Thanks, that blog is a useful resource to confirm ESRI's take on the issue. – Kelly Thomas Mar 6 '18 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.