1

SCRIPT TOOL IMAGE

I've developed a sort of find and replace script tool that functions. But I'm not able to figure out how to find empty cells in fields and replace them.For the "Find" parameter, I have tried entering "", '', None, null, and < null >.

An example that might make this clearer. Say you had a field in a table with every row empty, and you wanted to make them say "Not Available" or something instead, which you would enter in the "replace" parameter.

I feel like this is a tool/syntax issue and not really a script one, no?

import arcpy

FC = arcpy.GetParameterAsText(0)
oldText = arcpy.GetParameterAsText(1)
replaceText = arcpy.GetParameterAsText(2)
targetField = arcpy.GetParameterAsText(3)


queryString = targetField + ' = ' + "'" + oldText + "'"
try:

    with arcpy.da.UpdateCursor(FC, (targetField), queryString) as cursor:
        for row in cursor:
            if row[0] == oldText:
                row[0] = replaceText
                cursor.updateRow(row)

                arcpy.AddMessage("String where " + queryString + " found and      replaced with " + replaceText)

    except:
        arcpy.AddMessage(arcpy.GetMessages())
        arcpy.AddError("Failed to find and replace where " + queryString)
  • 1
    It depends on your database... shapefiles do not support Null values but geodatabases do. To select a Null value use 'is' i.e. Field is Null (converse Field is not Null). In a cursor row[n] == None (case sensitive) i.e. if row[0] == None: print "It's null" – Michael Stimson Mar 14 '16 at 3:32
  • Ah...but is there no way signify an empty cell in any sort of feature class, be it GDB, or shapefile, when using the tool interface? – De_Facto_Fish Mar 14 '16 at 3:48
  • 2
    That is true but you can tell if the feature class is shapefile, local or remote database using arcpy.Decsribe. d = arcpy.Describe(InFC), if d.dataType == "ShapeFile": if d.dataType == "FeatureClass": (geodatabase feature class).. You can do this in your validator script but it is probably best to branch out your code based on the dataType. Empty boxes in the tool dialog used to give parameter "#" (9.2) but I think now they use None, your script can interpret that as FindEmpty = True and then go and replace empty cells accordingly. – Michael Stimson Mar 14 '16 at 3:57
  • Alright, now where would that fit in my script above? – De_Facto_Fish Mar 14 '16 at 4:05
2

Expanding on the comments:

import sys, arcpy
FC = arcpy.GetParameterAsText(0)
oldText = arcpy.GetParameterAsText(1)
replaceText = arcpy.GetParameterAsText(2)
targetField = arcpy.GetParameterAsText(3)

if len(replaceText) == 0:
    replaceNone = True
else:
    replaceNone = False

try:
    if replaceNone:
        d = arcpy.Describe(FC)
        # branch for each database type
        if d.dataType == "ShapeFile":
            queryString = targetField + ' = \'\'' # empty string '' or not at all
            with arcpy.da.UpdateCursor(FC, (targetField), queryString) as cursor:
                for row in cursor:
                    if len(row[0].replace(" ","")) == 0:
                        row[0] = replaceText
                        cursor.updateRow(row)
                        arcpy.AddMessage("String where none found replaced with {0}".format(replaceText))

        elif d.dataType == "FeatureClass":
            queryString = targetField + ' is null'
            with arcpy.da.UpdateCursor(FC, (targetField), queryString) as cursor:
                for row in cursor:
                    row[0] = replaceText # go ahead and replace the null fields as decided by the cursor
                    cursor.updateRow(row)
                    arcpy.AddMessage("String where none found replaced with {0}".format(replaceText))
        else:
            arcpy.AddError("Unknown feature type")
            sys.exit(-1)

    else:
        # any string that is not None
        queryString = targetField + ' = ' + "'" + oldText + "'"
        with arcpy.da.UpdateCursor(FC, (targetField), queryString) as cursor:
            for row in cursor:
                if row[0] == oldText:
                    row[0] = replaceText
                    cursor.updateRow(row)
                    arcpy.AddMessage("String where " + queryString + " found and      replaced with " + replaceText)

except:
        arcpy.AddMessage(arcpy.GetMessages())
        arcpy.AddError("Failed to find and replace where " + queryString)

Because of the different handling of empty/null cells between databases and actual values it's necessary to branch early. Different query strings are needed for null values and, as I have said on your previous post, the whereclause can be omitted as it would just confuse the issue. It's better to test for empty/whitespace strings with len( and replace(" ","") operators to clear out just space strings but to leave any genuine strings... also replace(" ","").replace("\t","") might be a good idea but I haven't seen many fields filled with tab in a while so (being lazy) I skip it; I'm sure that will come back to bite me one day.

To put to rest (conclusively) the value of an optional string argument I created a simple script:

import arcpy

Par = arcpy.GetParameterAsText(0)

if Par == None:
    arcpy.AddMessage("Parameter is none")
else:
    if len(Par) == 0:
        arcpy.AddMessage("Parameter is 0 length string")
    else:
        arcpy.AddMessage("Parameter is {0}".format(Par))

Setup like this:

Tool setup showing optional parameter

and the results:

results of the tool

So it turns out that an optional unfilled string is a 0 length string, not None or '#', which changes the line if replaceText == None: to if len(replaceText) == 0: to test if the replaceText parameter of the tool is blank - insinuating Null, in the case where the replaceText is blank Null values in databases and empty/whitespace strings in shapefiles will be replaced. Note that this only works for strings... different strategies need to be employed with numeric and date fields, but that is a separate question by itself.

  • I am having trouble understanding what to use as inputs now though. Take a look at the image of my tool interface I added. When I put "", or '', or nothing at all in the Find parameter, and then choose the string field with empty cells in the replace parameter, and run the tool, those empty cells are not filled. – De_Facto_Fish Mar 14 '16 at 4:56
  • 1
    Make the field optional on your tool, by making it required (green dot, set by default) it means you MUST put a value into it. If the field is optional the tool can be executed with an empty box, which should be None but may not be. I've not had much call for empty boxes so I'm not 100%. Try making a small tool that just reports what an optional field is and see what you get. – Michael Stimson Mar 14 '16 at 4:59
  • I tried making it not required and nothing happened. I'm sorry, but I don't think your script here works for what I am trying to do. – De_Facto_Fish Mar 14 '16 at 5:33
  • @De_Facto_Fish There are some real subtleties between what script tools collect as parameters and what actually gets passed to your code. This is especially true when getting python to generate SQL expressions. I have a feeling that the most minor tweak to Stimson's answer will make everything work. I'll play with this today and get back to you. – Priscilla Mar 18 '16 at 13:45
0

I got the tool to work with a Shapefile! Set the "oldText" parameter to 'optional' in your tool settings. Then wrap the script in a function, in order to set default values if optional parameters are left blank. In this case, if oldText is blank, it will default to None. Test for None, to construct the correct SQL queryString for the cursor.

import arcpy

FC = arcpy.GetParameterAsText(0)
oldText = arcpy.GetParameterAsText(1)
replaceText = arcpy.GetParameterAsText(2)
targetField = arcpy.GetParameterAsText(3)


def FindReplace(FC, targetField, replaceText, oldText=None):
    if oldText == None:
        queryString = targetField + """ = '' """
    else: 
        queryString = targetField + ' = ' + "'" + oldText + "'"

    with arcpy.da.UpdateCursor(FC, (targetField), queryString) as cursor:
        for row in cursor:
            row[0] = replaceText
            cursor.updateRow(row)

FindReplace(FC, targetField, replaceText, oldText)

I really like what Michael Miles-Stimson answered about testing for Shapefile v. Feature Class. This is good advice if you want the tool to work for anything other than Shapefiles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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