Build a definition query from an existing spreadsheet

I wrote this script about 2 years ago, to set a long definition query on a layer from data contained in a spreadsheet. I can’t remember why I couldn’t join the data to the spreadsheet, but I’m in the process of adding all my scripts to GitHub, so even if I can no longer recall the reason for the script, the logic of it should still be able to help someone.

#
# @author Cindy Williams
# @date 25/06/2013
#
# Loops over an existing spreadsheet with attribute data
# and builds up a definition query to apply to the dataset.
#
# For use in the Python window in ArcMap.
#
from arcpy import da
import arcpy.mapping as MAP
# Input variables
xls = r"C:\Some\Arb\Folder\Data.xlsx\Sheet1$" # Spreadsheet
mxd = MAP.MapDocument("CURRENT") # Current map document
lyr = MAP.ListLayers(mxd)[0] # First layer in the the map
lst_qry = [] # List to hold the query
field_name = "Layer"
# Loops over the spreadsheet using a search cursor.
# Appends each value into the list
with arcpy.da.SearchCursor(xls, field_name) as cursor:
for row in cursor:
defn_qry = """ "{0}" = '{1}' """.format(field_name, row[0])
lst_qry.append(defn_qry)
# Build a string representation of the definition query and apply it to the layer
lyr.definitionQuery = ' OR '.join(lst_qry)
# Refresh ArcMap to see the query applied
arcpy.RefreshActiveView()

A few things to note – Line 12:

import arcpy.mapping as MAP

I went through a phase where most of my scripts involved data driven pages, and I was getting tired of typing out the full reference. However, I have since returned to the more verbose way of typing it because my laziness should not violate Python’s philosophy of readability and there are so many ways to import that it doesn’t matter in these tiny scripts.

lyr.definitionQuery = ' OR '.join(lst_qry)

I used join to create a bulky SQL query. This is terrible, but it gets the job done, and it helped me evolve my understanding of the peculiarities of using SQL in ArcGIS.

One thought on “Build a definition query from an existing spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.