How to iterate fields and remove Null values and spaces

Question

Is it possible to iterate fields in a table to remove Null values and spaces (where there is no value)? In other words, I would like to replace Null and " " with "" (no space).

Cobbling arcpy scripts, I have this:

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc)
for field in fieldList:
    with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
        for row in cursor:
            if row[0] == None:
                row[0] = ''
            elif row[0] == ' ':
                row[0] = ''
                cursor.updateRow(row)

print "Processing complete"

I'm aware of using "remove" in the field calculator, but you have to go field by field. I'd like to do this for the whole table.

Answer 1

I think you've got it the wrong way around. Not for field in fieldList then for row in cursor, rather for row in cursor and for field in fieldList except you will need to index properly... you're only trying to adjust the first field in the fieldList with row[0].

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc)
# remove some key fields from the list
desc = arcpy.Describe(fc)

fieldList.remove(desc.shapeFieldName)
fieldList.remove(desc.OIDFieldName)

with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
    fRange = range(len(fieldList)) # create an index 0 to the number of elements in fieldList - 1

    for row in cursor:
        SomethingUpdated = False # a flag for changes

        # step through each field in the row by its index
        for index in fRange:
            if row[index] == None:
                row[index] = ''         #set the field to empty string
                SomethingUpdated = True #flag to store row
            else:
                val = str(row[index]).replace(" ","") # remove spaces
                if len(val) == 0:
                    # value is nothing but spaces or empty
                    row[index] = ''         #set the field to empty string
                    SomethingUpdated = True #flag to store row

        if SomethingUpdated:
            cursor.updateRow(row)

print "Processing complete"

Be aware that no checking is being done for field types; trying to set '' into a numeric field will cause this script to crash hard!

Answer 2

I shall offer my variation:

import arcpy

fc = "{path to geodatabase and feature class}"

fieldList = [f.name for f in arcpy.ListFields(fc) if f.type == "String"]

if fieldList:

    with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
        for row in cursor:
            for i in range (len(fieldList)):
                if not row[i]:
                    row[i] == ""
                elif row[i] == " ":
                    row[i] == ""
            cursor.updateRow(row)

print "Processing complete"

Answer 3

I'm not sure that I understand what you are asking, because your question does not describe what is not working in your code, but I think:

    for row in cursor:
        if row[0] == None:
            row[0] = ''
        elif row[0] == ' ':
            row[0] = ''
            cursor.updateRow(row)

needs to be:

    for row in cursor:
        if row[0] == None:
            row[0] = ''
        elif row[0] == ' ':
            row[0] = ''
        cursor.updateRow(row)

You will also need to filter out text fields from your other fields because they are the only ones you can write strings to.

Answer 4

I don't believe that it's possible to calculate for an entire table at once, you will still need to calculate field by field.

As far as actually performing the edit the best way to update the data would be with arcpy.CalculateField_management - which is effectively the field calculator in your Python script - rather than an Update Cursor (which has to operate row at a time). You can use a Python expression in arcpy.CalculateField_management (or the field calculator itself) to check a string and return the updated string, for example:

(!field_name! or "").strip() # will also strip the trailing spaces

Which you can throw at arcpy.CalculateField_management in a script;

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc, field_type="String") # don't want to try this on an integer

for field in fieldList:
    expression = """(!{}! or "").strip()""".format(field.name)
    arcpy.CalculateField_management(fc, field.name, expression, "PYTHON_9.3")

Answer 5

fc = inFeature # feeature class containing the null values
Row = # field containing the null values
selection = arcpy.SelectLayerByAttribute_management(fc, "New_Selection", "Row is NULL") # selects the rows with null values
arcpy.DeleteRows_management(fc) # deletes selected rows