topic Re: Can't calculate text into string field using CalculateField in Python Questions

Can't calculate text into string field using CalculateField

StephenRhone — Wed, 19 Jun 2019 17:09:46 GMT

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.

Re: Can't calculate text into string field using CalculateField

DanPatterson_Retired — Wed, 19 Jun 2019 17:13:28 GMT

The details are most likely in the script. Could you post it and/or the relevant sections

Re: Can't calculate text into string field using CalculateField

StephenRhone — Sat, 11 Dec 2021 07:19:19 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Can't calculate text into string field using CalculateField

DanPatterson_Retired — Wed, 19 Jun 2019 22:44:20 GMT

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

Re: Can't calculate text into string field using CalculateField

StephenRhone — Thu, 20 Jun 2019 15:47:49 GMT

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

Re: Can't calculate text into string field using CalculateField

LukeWebb — Fri, 21 Jun 2019 11:53:25 GMT

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.

Re: Can't calculate text into string field using CalculateField

StephenRhone — Fri, 21 Jun 2019 14:31:19 GMT

I should've known it'd come down to a syntax quirk - that did the trick! Thanks so much for your help.