ArcMap: Dealing with NULLs

NULL values in GIS data can trip you up if you don’t know how to deal with them.  A NULL value is not the same as a blank (empty) value, and actually takes up 1 bit more of space than an empty value.  Allowing a field to be NULL introduces an additional state that you would not have if the field was created to not allow NULLs.  And yes, you can have NULLs in numeric fields too!

Why have a NULL value in your data?  Usually this is used to mean “not set” or “uninitialized”.  A good example would be an employee database with a termination date field that is set to NULL when the employee is new.  Another example would be an elevation field.  You could use a NULL value to represent “not known”, since zero is a valid elevation.  More than likely you have fields that allow NULLs because someone created them not paying attention to the defaults.

So how do you specify a field to allow NULLs or not?  Open ArcMap (I’m using version 10.2.2) and bring in some data from a geodatabase.  Then open the data’s attribute table and select “Add Field…” from the Table Options icon.


Note by default in the Field Properties that “Allow NULL Values” is set to “Yes”.  If you do not want NULLs, set it to “No”.  If you set it to “No” and do not specify a “Default Value”, then Text fields will default to a blank (empty) value and numeric fields will default to zero.  Also, keep in mind that you can add a field with “Allow NULL Values” set to “No” only if your feature class or table is not populated yet with records.

So let’s take a look at some test data I created with some fields that allow NULLs.


As you can probably guess, the field TFIELD is a text field and NFIELD is a numeric field.  Note some records have NULL values while others have data.

Using the Select By Attributes tool, I can select NULL values in the TFIELD by constructing the following SQL expression.


The records with NULL in the TFIELD are selected.


If I wanted to select everything but NULL, I would have used this SQL expression.



Numeric fields work the same way when selecting NULLs.



Now let’s take a few records and calc the TFIELD value to blank.  First I select a few records.


Then right click on the TFIELD name in the attribute table and select “Field Calculator…”.  Then in Field Calculator I specify TFIELD = “” (that is two double quotes with no space between them).


Note the values change from NULL to blank.


Now let’s select NULL values in TFIELD again with the Select By Attributes tool using “TFIELD IS NULL”.  Look what happens.


Note the blank records were not selected.  This shows that blanks are not the same as NULLs.  You have to be careful of this.  If you wanted all blanks and NULLs, try this expression in the Select By Attributes tool.


Note in this tool I had to use two single quotes with no spaces between for the SQL expression.  This selects all NULLs and blanks.


What if I wanted to calc a NULL value to a field?  First I will select some records, like my blank values in the TFIELD, then using the Field Calculator I specify TFIELD = NULL.



I can do the same for the numeric field.  First I will select all records with the value of 55.


Then using the Field Calculator I specify NFIELD = NULL.  All values of 55 are changed to NULL.


So there you have it.  You are now an expert with dealing with NULLs and hopefully when you setup your data, you will know when to use them and NOT NULL!

14 thoughts on “ArcMap: Dealing with NULLs

  1. G’day to you.
    we had to deal with an Excel table. During export or joining or whatever, a lot of empty values were assigned to the attribute table of our feature class. Is there a way to set ALL the empty values in the table to nulls? I’m not asking how to calculate field by field (we have SIXTY fields). I’m looking for something quicker.
    Thank you in advance 🙂

      • Ha! Lucky for you I had some time on my hands. This is what you need to do. Open ArcMap and add the data layer to the map. Next open the Python window in ArcMap by clicking the button at the top (next to the ArcTools button). In the Python window, enter this line:

        layer = "your_layer_name_here"

        Just replace your_layer_name_here with your layer name in ArcMap. Hit the Enter key. Next just copy this code below and paste it in the Python window, then hit the Enter key:

        fields = [ for f in arcpy.ListFields(layer, "", "String")]
        for i in fields:
          param = "\"" + str(i) + "\" = ''"
          print("Selecting field {}...".format(i))
          arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION", param)
          selrec = int(arcpy.GetCount_management(layer).getOutput(0))
          if selrec > 0:
            print(" Calcing {} records".format(selrec))
            arcpy.CalculateField_management(layer, i, "None", "PYTHON", "")

        The script will run through all your text fields, select records that have a blank value, and if selected calc a new value of NULL.

        You owe me a virtual beer! -mike

  2. Mkay, now I could just kill myself! I wasn’t notified of your reply. After wasting like two months Googling my ass off and tinkering with Python, I finally found the correct code. I was of to tell you when I saw your reply ×D In any case, thank you for your trouble.

    My code is like totally different, so I’m still gonna paste it here:

    # Imports the necessary module.
    import arcpy

    # Specifies the path to the feature class.
    path = r’D:\Marko\python\python.gdb\staro’

    # Lists all the fields, and creates an empty array to store all the field values.
    fieldObs = arcpy.ListFields(path)
    fieldNames = []

    # Loops through the fields, and determine the count for the values.
    for field in fieldObs:
    del fieldObs
    fieldCount = len(fieldNames)

    # Creates a new loop to find and replace the null fields with the desired value using the UpdateCursor() function.
    with arcpy.da.UpdateCursor(path, fieldNames) as curU:
    for row in curU:
    rowU = row
    for field in range(fieldCount):
    if rowU[field] == ” “:
    rowU[field] = None


    # Deletes the created cursor to release the lock on the feature class.
    del curU

    Once again thanks! 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s