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()

view raw

buildDefQryFromXLS.py

hosted with ❤ by GitHub

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.

Total Count of features in layers in a mxd

After converting a lot of data from CAD and formatting it, I appended the four feature classes I had into a template feature class using field mapping. I wanted to do a quick check to determine if the number of features in the individual feature classes matched up with the number in the target dataset.

import arcpy

mxd = arcpy.mapping.MapDocument("CURRENT")
n = 0 #cumulative total of all features in the mxd

for lyr in arcpy.mapping.ListLayers(mxd):
    
    #Use Get Count to return a Result containing the number of rows in the layer.
    #Cast the output to an int
    
    i = int(arcpy.GetCount_management(lyr).getOutput(0))
    
    #Get a cumulative total of all the features in the mxd.
    n += i
    
    #print the result
    print "{0} has {1} features. Total feature count = {2}".format(lyr, i, n)

The sum total of the features of the individual feature classes was different to those in the appended dataset, so I wanted to check how big the discrepancies were in terms of number missing per feature class.

fc = arcpy.mapping.ListLayers(mxd, "myLayer")[0]
myField = "my_field"

for u in set([row[0] for row in arcpy.da.SearchCursor(fc,myField)]):
    sql_clause = """ "{0}" = '{1}' """.format(myField,u)
    arcpy.SelectLayerByAttribute_management(fc, "NEW_SELECTION",sql_clause)
    i = int(arcpy.GetCount_management(fc).getOutput(0))
    print "There are {0} features in {1}".format(i,u)

Create Definition Query from values in Excel

I received a shapefile containing 50 000 records which had been converted from a CAD master plan. The Layer field contained 102 unique values, but for my purposes I only needed to look at 52 of those values (and then start cleaning the data). The engineer gave me an excel document with the values I needed, so I wrote this script to set a definition query on the layer:

xls = r"C:\Documents\MyExcel.xlsxSheet1$"
mxd = arcpy.mapping.MapDocument("CURRENT")
lyr = arcpy.mapping.ListLayers(mxd,"my_layer")[0]
lst_qry = []

with arcpy.da.SearchCursor(xls,("Layer")) as cursor:
    for row in cursor:
        #build up the query for each unique value
        defn_qry = """ "Layer" = '{}' """.format(row[0])
        lst_qry.append(defn_qry)

#create a string from the values in the list by
#concatentating with OR and set as definition query
lyr.definitionQuery = ' OR '.join(lst_qry)

Even though I detest using it, I ran this in the Python Window in ArcMap as it’s really the quickest way to work some Python magic on layers in the current document.