Does ArcMap have search and replace function over several fields at once for NULL values?

Oh-No 08/14/2017. 2 answers, 70 views
arcgis-desktop arcpy arcmap null find

I am trying to replace all "Null" values to "0", over all fields. I am quite sure this is wishfull thinking, but it's worth asking ! Is there a tool which performs this ? (as well as the opposite operation) ?

5 Comments
PolyGeo♦ 08/14/2017
For a long time I thought that it did not but I am pretty sure that this has been asked here before, and that it does - at least for strings. I'm in the middle of something at the moment so will try to get back and search for the duplicate, unless you or someone else gets to it before I do.
Michael Stimson 08/14/2017
If you export to a shapefile the fields will no longer be Null as shapefiles do not support null values.. numeric fields will be 0 and text fields will be a 0 length string. Do you have any python ability? This would be very simple to do with a python script. Find & replace should work on all fields (except geometry and ObjectID) unless constrained to specific fields.
Oh-No 08/14/2017
PolyGeo, I don't find duplicates trating several fields at once. I might be overlooking them. @MichaelStimson. I am learning python, but not fast enough to solve all my problems. I can replace it through one field but not iterate this through all my columns. Anyway, I exported my file out of my gdb and indeed there are no more Null values. I am just very confused why they where there before then. I was convinced the files in my gdb where shp.
1 PolyGeo♦ 08/14/2017
gis.stackexchange.com/questions/55155/… is a Q&A in the same vicinity but is not what I was thinking of. I seem to recall it being something on Table Options but have not yet checked.
1 PolyGeo♦ 08/14/2017
It looks like it will not work with nulls (just strings) but this is what I was thinking of: support.esri.com/en/technical-article/000012044

2 Answers


Michael Stimson 08/14/2017.

@Aarons' script will work (mostly) but will include non-editable fields (like shape_lengh, shape_area etc..) and will fall over the first time it encounters a Null in a string (text) field but does contain the basics ready to be built upon.

This would be my approach:

import os, sys, arcpy

InLayer = sys.argv[1] # the layer or feature class to update

# find out what the OID and Geometry fields are
# to skip checking these ones. I'm doing most string
# comparisons in upper case so these names need to
# be recorded in upper case.
d = arcpy.Describe(InLayer)
OIDname  = d.OIDFieldName.upper()
GeomName = d.shapeFieldName.upper()

FieldNames = [] # empty list ready to be filled with field names
FieldTypes = {} # empty dictionary to be filled with field types

for Field in arcpy.ListFields(InLayer):
    FName = Field.name.upper()
    if Field.editable:
        if FName != OIDname and FName != GeomName:
            # Not an upper case comparison as these values are returned from a function
            # and can be reasonably expected to be in the correct case.
            if Field.type in ['Double','Integer','Single','SmallInteger','String']:
                FieldNames.append(FName)         # add the field name to the list to be processed
                FieldTypes[FName] = Field.type   # record the field type as a dict for later use

ItList = range(len(FieldNames))          # iterator for the valid fields with the length of field names

with arcpy.da.UpdateCursor(InLayer,FieldNames) as UCur:
    for URow in UCur:                 # cursor through the rows
        SomethingChanged = False        # Keep track of anything changed, no need to store unmodified rows
        for ThisIdx in ItList:          # iterate the range 0 to number of fields - 1
            if URow[ThisIdx] == None:     # if the value for this row at this index is Null
                FName = FieldNames[ThisIdx] # find the matching field name, used as a key for dict
                FType = FieldTypes[FName]   # get the field type from the dictionary.
                if FType == 'String':       # strings need to be replaced with an empty string
                    URow[ThisIdx] = ''        # replace the Null with an empty string
                    SomethingChanged = True   # set the flag to update this row
                else:                       # if the field is not a string it must be numeric
                    URow[ThisIdx] = 0         # replace the Null with 0 for a numeric field
                    SomethingChanged = True   # set the flag to update this row
    if SomethingChanged:
        UCur.updateRow(URow)            # store the updated values if anything has changed

It's a little more long-winded but contains some checks to avoid the common failures.. I intend this script to be used as a learning experience, thus the copious comments.

5 comments
Aaron♦ 08/14/2017
Well, this is thorough! Could you elaborate on how my script will fail on text fields (I tested the script on text fields)? Also, the non-editable fields should not be an issue as there should never be a Null value.
Michael Stimson 08/14/2017
@Aaron it's true that the non-editable fields shouldn't be Null but in the case of an empty geometry (which is an error in itself) the shape_length and shape_area will be Null. A string cannot store a numeric value, you will get a TypeError when you try to update the row, at least that is my assumption, but python might be intelligent enough to convert the 0 to a string but then you won't have an empty string ('') you will have 0 stored ('0') as text - which may or may not be what you're after.
1 Aaron♦ 08/14/2017
In the Zen of Python (Item 3) available from import this: Simple is better than complex;) There is no issue with storing 0 in a text string on my machine running ArcGIS 10.5.1.
1 Aaron♦ 08/15/2017
I have added a screenshot showing the script working without errors under multiple field types and non-editable fields.
Michael Stimson 08/15/2017
Oh I see, not a TypeError but the string '0', which may or may not be what you're after but I think it's important to note that storing a numeric value in a text field does not raise an exception. Have you tried it with empty geometries and Blob or Raster fields?

Aaron 08/15/2017.

The following approach iterates through all the fields and converts <Null> to 0:

import arcpy

fc = r'C:\path\to\your\geodatabase.gdb\test_fc'

fields = [x.name for x in arcpy.ListFields(fc)]

for f in fields:
    with arcpy.da.UpdateCursor(fc, f) as cursor:
        for row in cursor:
            if row[0] is None:
                row[0] = 0
            cursor.updateRow(row)

The following shows the results of the script with a combination of non-editable fields and fields of short, long, float, and text types.enter image description hereenter image description here

Related questions

Hot questions

Language

Popular Tags