How to keep a parameter blank in a script tool?

Question

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)

• Michael Stimson: 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"
• De_Facto_Fish: 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?
• Michael Stimson: 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.
• De_Facto_Fish: Alright, now where would that fit in my script above?

Answer 1

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.

• De_Facto_Fish: 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.
• Michael Stimson: 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.
• De_Facto_Fish: 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.
• Priscilla: @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.

Answer 2

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.