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.

One thought on “Create Definition Query from values in Excel

Leave a comment

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