Good morning,
I am about to pull my hair out because in my newest script, I am attempting to use CalculateField to add text to a text field, and am getting some maddeningly inconsistent results. The field length is 3, and I am attempting to simply calculate Yes or No into it, but it comes back with nulls every time I run it, with no error messages from the script. However, the script also calculates a second text field with a length of 5 that is meant for zip codes (it is text in order to accommodate Canadian FSAs), and this calculation is successful for American-style five digit numeric zip codes.
So I tried using the Python interface in ArcMap to manually calculate the field in the layer, and my results are shown below.
Integer, or integer within single quotes:
Successful calculation.
String with all letters ('Yes', 'No', 'ABC'), or alphanumeric string beginning with a letter ('A30'):
Empty value for ObjectID = 1
The calculated value is invalid for the row with ObjectID = 1. For example, the calculated value may be too large for the field or you may be trying to add a string to a number field. This row will not be updated.
Do you want to process the rest of the rows? If the problem re-occurs while processing, this message will not be displayed. No messages will be generated if you click Yes. Click No to abort.
Alphanumeric string beginning with a number ('30A'):
General error executing calculator.
ERROR 999999: Error executing function.
Expected end of statement
Failed to execute (CalculateField).
Again, these are string fields that should be able to accept any alphanumeric value I want to calculate into them. I should also mention that if I edit the layer directly in ArcMap, I can successfully calculate text into the field that way, but I need the script to be able to do this as it is part of a cursor that repeats numerous times. Has anyone else out there run into this problem?
Thank you in advance for your assistance.
Solved! Go to Solution.
Instead of:
arcpy.CalculateField_management(TempFC, "StoreID", Store)
Try:
arcpy.CalculateField_management(TempFC, "StoreID", '"%s"' % Store)
I think when using strings instead of fields, you need to put the " " around the string, the same as if you were manually using field calculator.
The details are most likely in the script. Could you post it and/or the relevant sections
Below is the relevant code. In the store list table, the StoreID field is a four-digit number, but is stored as a text field, and MKTZip is the Yes/No field. For each row in the cursor, it gets the store number and Yes/No value from the table, selects zip codes that meet those criteria, exports those to a temporary feature class, and is supposed to calculate the store number and Yes/No flag into the appropriate fields on lines 41 and 42. The store number works, the Yes/No doesn't.
I know that the PrimaryFlag field is not null because when I have the script print the selection query in lines 13 and 14, the yes/no value shows up properly.
I have even tried replacing the field name with {0}.format(PrimaryFlag), but this yields the same results.
# Populate zip code assignment feature classes
timeprint("Populating zip code assignment feature classes")
with arcpy.da.SearchCursor(StoreListTable, ['StoreID', 'MKTZip']) as cursor:
for row in cursor:
# Set up cursor variables and temporary feature class and table
Store = row[0]
PrimaryFlag = row[1]
TempZipTableName = "Zip_Table_" + Store + "_" + PrimaryFlag
TempZipTable = OutGDB + "\\" + TempZipTableName
TempFCName = "Zip_FC_" + Store + "_" + PrimaryFlag
TempFC = OutGDB + "\\" + TempFCName
# Create temporary zip table of selected zips
SelectQuery = "StoreID = '{0}' AND MKTZip = '{1}'".format(Store, PrimaryFlag)
print " " + SelectQuery
arcpy.SelectLayerByAttribute_management(OutZipTableView, "NEW_SELECTION", SelectQuery)
arcpy.TableToTable_conversion(OutZipTableView, OutGDB, TempZipTableName)
arcpy.SelectLayerByAttribute_management(OutZipTableView, "CLEAR_SELECTION")
# Build zip query and select zips from zip feature classes
ZipQuery = "ZIP IN ('"
with arcpy.da.SearchCursor(TempZipTable, ['ZipCode']) as zipcursor:
for ziprow in zipcursor:
zip = ziprow[0]
ZipQuery += zip + "', '"
del zipcursor
ZipQuery = ZipQuery[:-3]
ZipQuery += ")"
arcpy.SelectLayerByAttribute_management(OutAreaZipLayer, "NEW_SELECTION", ZipQuery)
arcpy.SelectLayerByAttribute_management(OutPointZipLayer, "NEW_SELECTION", ZipQuery)
# Set up procedure to populate the zip assignment feature classes
def PopulateAssignedZips(Lyr, FC):
# Get count of selected zips
ZipCount = arcpy.GetCount_management(Lyr)
ZipInt = int(ZipCount.getOutput(0))
# Proceed only if zips were selected
if ZipInt > 0:
# Export to temporary feature class
arcpy.FeatureClassToFeatureClass_conversion(Lyr, OutGDB, TempFCName)
# Clear selected features from zip layer
arcpy.SelectLayerByAttribute_management(Lyr, "CLEAR_SELECTION")
# Add store number and primary flag
arcpy.CalculateField_management(TempFC, "StoreID", Store)
arcpy.CalculateField_management(TempFC, "MKTZip", PrimaryFlag)
# Append selected zips into zip assignment feature class
arcpy.Append_management(TempFC, FC, "NO_TEST")
# Populate the zip assignment feature classes
PopulateAssignedZips(OutAreaZipLayer, OutStoreAreaZipFC)
PopulateAssignedZips(OutPointZipLayer, OutStorePointZipFC)
# Delete temporary zip table and feature class
arcpy.Delete_management(TempZipTable)
arcpy.Delete_management(TempFC)
del cursor
why is your 'def' (lines 30-44) within your cursor?
hard to follow, so I would throw a print statement in there so that the output going into calculate field can be seen.
And since you are trying to put X characters into a field of X width/size... does it work with a field of X+1 (or 2) in size?
I am still questioning why the field size has to be the same width as your text
Hi Dan,
Thanks for taking a look at my code - I guess I put the function definition within the cursor for coding expediency. And I tried increasing the field width, but this didn't change the results.
Ultimately I was able to devise a workaround - since it appears I can calculate numeric values into the fields, even though I should be able to add text, I just had it put a 1 into the field where the flag value was "Yes," with 0 for "No." Then after the cursor finished, I added a new text field to the feature class and had it calculate to Yes/No based on the 1/0 values in the other field - this was successful.
It is still bizarre that it wouldn't let me put text into a string field within the cursor, but at least I now have a working script that gives me the results I need, even if I had to go about it in a roundabout way. Thanks for all your help; have a blessed day. 🙂
Instead of:
arcpy.CalculateField_management(TempFC, "StoreID", Store)
Try:
arcpy.CalculateField_management(TempFC, "StoreID", '"%s"' % Store)
I think when using strings instead of fields, you need to put the " " around the string, the same as if you were manually using field calculator.
I should've known it'd come down to a syntax quirk - that did the trick! Thanks so much for your help.