3

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

Improve this question
0

1 Answer 1

Reset to default
2

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

Excel tables behave in the same way as other tables that don't have an ObjectID field. This means you will be unable to edit, perform relates, or make selections on the map.

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.

Improve this answer
3
  • I will give this a shot. It seems weird though that the ModelBuilder would work, yet when I run the exact Python code it outputs, it gives me an error. Do you recommend csv files instead, or no? I assume that would have the same exact issues.
    – Shawn Schreier
    Jul 23, 2020 at 1:39
  • If you are planning to use CSVs in your joins, it will yield the same issue. However, if you prefer a complete python approach, there are very good libraries to process CSVs and usually I use CSVs for simple tabular data together with cursors in arcpy's data access module (desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-data-access/…).
    – fatih_dur
    Jul 23, 2020 at 2:02
  • So, I've realized it has something to do with the join. When I create the join, it is successful, and the columns and data are there. I can also use the select tool, and have no issues using columns that were on the original table. However, if I try to use a column from the joined data, I receive that error. Any thoughts on why it works perfectly for columns from the original table, but not with the newly joined columns? I've tried using both the name and the column alias with no success.
    – Shawn Schreier
    Aug 24, 2020 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.