Select by character limit

Question

I'm using the select by attributes to find unique values that I am copy pasting from a spreedsheet.

EX "jobnumber" = 1111116 OR "jobnumber" = 2222245 OR "jobnumber" = 5555655 .. etc

The problem is I have thousands of values and hit the character limit. I can do select by attribute again and just add to the selection, but this would require doing many, many times and would be pretty tedious.

I want to know if there is a better way of getting large numbers of unique values selected all at the same time, maybe using ArcMap's Python console, or making the spreedsheet into a table in ArcMap and relating it to the target layer.

Answer 1

This should work:

1. Add the excel file to ArcMap
2. Copy excel table to a file geodatabase
3. Join the copy to your layer
4. Select by attribute rows that are not Null in the jobnumber column

But if you want to use arcpy add the excel and layer to ArcMap and paste this in the Python window after adjusting commented lines:

import arcpy

excel = 'Data$' #Change
excel_fieldname = 'jobnumber' #Change
layer = 'Polygons123' #Change
layer_fieldname = 'jobnumber' #Change

jobnumbers = [str(i[0]) for i in arcpy.da.SearchCursor(excel, excel_fieldname)]
sql = """{0} IN({1})""".format(arcpy.AddFieldDelimiters(layer, layer_fieldname),','.join(jobnumbers))
arcpy.SelectLayerByAttribute_management(layer,where_clause=sql)

Im using the IN operator:

The IN operator is a shorthand for multiple OR conditions.