I have some code that ran just fine on ArcMap 10.7 in May, but is now giving me an error when trying to create a new shapefile. The join that precedes the code that throws an error succeeds, and when I look at the attribute table the column exists as it should. I've reviewed other questions here but cannot seem to figure out the issue.
import arcpy
import time
beginningTime = time.time()
arcpy.env.overwriteOutput = True
mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd)[0]
excel_file = "C:\\Users\\SSchreier\\Desktop\\ArcGIS Maps\\MTW\\MTW_Map.xlsx"
county = "C:\\Users\\SSchreier\\Desktop\\ArcGIS Maps\\County_Projected_AEAC"
arcpy.AddJoin_management("County_Projected_AEAC", "ID", "C:/Users/SSchreier/Desktop/ArcGIS Maps/MTW/MTW_Map.xlsx/Sheet1$", "modified_FIPS", "KEEP_ALL")
arcpy.Select_analysis("County_Projected_AEAC", "C:/Users/SSchreier/Desktop/ArcGIS Maps/MTW/Beef_Cattle.shp", '"Sheet1$.Flag_Beef_Cattle" = 1')
The last line throws the error that the column does not exist. However, it is in the attribute table, and capitalization and underscores and everything have been verified. I also verified the SQL formula outside of the Python script and it verified successfully. The error is below:
Runtime error Traceback (most recent call last): File "",
line 12, in File "c:\program files
(x86)\arcgis\desktop10.7\arcpy\arcpy\analysis.py", line 90, in Select
raise e ExecuteError: ERROR 000358: Invalid expression
"Sheet1$.Flag_Beef_Cattle" = 1 Failed to execute (Select).
I also tried the following code because this is the script the ModelBuilder output, but still receive an error, albeit a different error.
arcpy.Select_analysis("County_Projected_AEAC", "C:/Users/SSchreier/Desktop/ArcGIS Maps/MTW/Beef_Cattle.shp", "\"Sheet1$.Flag_Beef_Cattle\" = 1")
Runtime error Traceback (most recent call last): File "",
line 17, in File "c:\program files
(x86)\arcgis\desktop10.7\arcpy\arcpy\analysis.py", line 90, in Select
raise e ExecuteError: ERROR 000358: Invalid expression
"Sheet1$.Flag_Beef_Cattle" = 1 Failed to execute (Select).
Best Answer
To my experience, using MS Excel sheet in ArcGIS may cause issues sooner or later, since Excel sheet is not literally a database. A recent edit in your sheet may have caused this issue. If you visit this very informative page, https://desktop.arcgis.com/en/arcmap/latest/manage-data/tables/understanding-how-to-use-microsoft-excel-files-in-arcgis.htm, it states
In your case, unable to make selection could be the problem. As a long run resolution, I suggest exporting Excel sheet beforehand via Excel to Table tool.