Problem: Definition queries on text based data, such as CSVs, in older map documents are no longer valid in ArcGIS 10.1 or 10.2
Before ArcGIS 10.1, when a definition query from a CSV file was set on a table or XY event layer, the field name would be placed between two accent marks. For example:
`type` = 'Highway'
In ArcGIS 10.1 and newer versions, this is no longer recognized as a valid SQL statement. Instead, the software requires that the field name be placed between double quotation marks.
This can be seen when comparing the Query Builder statements for a CSV based table between ArcGIS 10.0 and 10.1. In ArcGIS 10.0, Query Builder uses accent marks around the field name; whereas in ArcGIS 10.1, it uses double quotes.
At ArcGIS 10.1, SQL statements for text based data conform more to the SQL92 standard, which requires text based data to use double quotation marks. According to the section "ASCII or text file tables" in this What's new in ArcGIS document, "accessing data in delimited text files and working with them as input for a layer has been made simpler at ArcGIS 10.1. Rather than using the Microsoft OLE DB provider for Open Database Communication (ODBC) drivers and the Microsoft ODBC Text Driver for text files to access tabular data in text files, ArcGIS reads these files directly."
This allows text files to be used in a way that is more comparable to other data types used in ArcGIS.
Solution or Workaround
SQL statements for text based data, such as CSV files, require the use of double quotation marks around the field names instead of accent marks.
Python can be used to update older maps that contain definition queries written with quotation marks rather than double quotation marks.
Since definition queries are stored in the layer object within the map, this sample can be used from the Python window in an open map to update the accent marks with double quotation marks.
mxd = arcpy.mapping.MapDocument("Current")
layers = arcpy.mapping.ListLayers(mxd)
for layer in layers:
dq = layer.definitionQuery
layer.definitionQuery = dq.replace('`', '"')
TableViews = arcpy.mapping.ListTableViews(mxd)
for table in TableViews:
dq = table.definitionQuery
table.definitionQuery = dq.replace('`', '"')