Honoring default values when loading data into a file geodatabase

2979
2
Jump to solution
04-27-2016 10:45 AM
ShannonPugh
Occasional Contributor

I have an empty file geodatabase that I am loading data into. Many of the fields in the target FGDB have default values assigned. I'd like for those to be honored when loading data. Note that the fields in question do not have corresponding fields in the data being loaded, so no values will be brought over from it. I end up with null values instead of the defaults. Is there a way to accomplish this other than calculating each field outside of the append or load commands?

1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor

You cannot set up the Load to do what you want, since it is just like using Add field on the original table (everything comes in Null).  If the target you are loading is empty when you set the field to not allow Null values it will default to a space if it is text, or 0 if it is a number, not the default value.

If you have a lot of fields to change from Null to the default you should use a cursor, not the field calculator, since the cursor can evaluate and set every field in a record in a single pass through the records, while the field calculator has to process all of the records for each time a field is calculated.

View solution in original post

2 Replies
RichardFairhurst
MVP Honored Contributor

You cannot set up the Load to do what you want, since it is just like using Add field on the original table (everything comes in Null).  If the target you are loading is empty when you set the field to not allow Null values it will default to a space if it is text, or 0 if it is a number, not the default value.

If you have a lot of fields to change from Null to the default you should use a cursor, not the field calculator, since the cursor can evaluate and set every field in a record in a single pass through the records, while the field calculator has to process all of the records for each time a field is calculated.

curtvprice
MVP Esteemed Contributor

Honestly that would be a nice geoprocessing tool to have: "Replace Null with Defaults"

Here's an untested crack at it. Note that this will not work with subtypes - that would require a bit more work.

If you're not into Python scripting, you could paste this code into the Calculate Value tool with an expression like:

ReplaceNullWithDefaults(r"%input table%")

def ReplaceNullWithDefaults(tbl):
    # create list of fields that have a geodatabase default value
    fields = [f for f in arcpy.ListFields(tbl) if f.defaultValue != None and f.isNullable]
    fnames = [f.name for f in fields]
    with arcpy.da.UpdateCursor(tbl, fnames) as rows:
        for row in rows:
            for k in range(len(fnames)):
                if row == None:
                    row = fields.defaultValue
            rows.updateRows(row)