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!
SoCalGIS.org 
How can I prevent Null form being coerced to zero when exporting an attribute table to a text file?
I don’t think you can for numeric fields that have nulls during the export from ArcMap.
Thanks for the reply. That’s unfortunate. ESRI really needs to step their game up!
I know. Since nulls in text fields come across as null, you would think they could do the same for null numeric fields as well.
How can i set the default text field to empty not Null ?
Hi Ahmed. As explained in the 4th paragraph in the post, when you add a field, set the Allow NULL Values option to No. Note you can only do this with an empty feature class or table. -mike
refusing for numeric values
Thanks!
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 🙂
Hi Marko. Other than writing a python script that would step through each field and select the empty values and calc them to NULLs, all you can do is the manual process! -mike
Mkay, where can I get the script? 😀
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 = [f.name 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
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:
fieldNames.append(field.name)
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
curU.updateRow(rowU)
# Deletes the created cursor to release the lock on the feature class.
del curU
Once again thanks! 🙂
Glad you found your solution! -mike
I just recently saw this post and wanted to make two comments based on my experience dealing with NULLs in an a large EGDB.
1. Watch out for the insidious “” string value in your data. Sometimes new editors (interns, etc..) see in the Feature Classes and end up typing that string in. Use a DefQ to set the attribute = “” Then use Field Calculator to set it to NULL (or None if using Python, as seen above.)
2. Similar to above, a lot of times folks will enter a space or multiple spaces when perhaps trying to enter a blank field. I typically use a trim function to check for a true empty space which can then be selected and set to a NULL if that’s your intent.
Great points Paul! -mike