FGDB FC field name too long for Oracle SDE

185
5
Jump to solution
11-19-2018 09:26 AM
forestknutsen1
MVP Regular Contributor

We have some python code the scrapes fgdb's off a public site and loads them into an oracle sde. However, one of the feature classes has a very long field name that is making arcpy copy features puke. What is the best way to truncate this field name?

I have thought of a few options -- none of which I am thrilled with...

1) export to shapefile then copy that into sde

2) read the fc shcema info with arcpy and build a new fc with that truncates field names as needed. Then append the original data into the new fc with field mapping objects

Anyone have a more elegant solution? 

0 Kudos
1 Solution

Accepted Solutions
forestknutsen1
MVP Regular Contributor

People did not like the shapefile solution... So...

def field_validation(feature_class):
    field_list = arcpy.ListFields(feature_class)
    validation = True
    logger.info(ceaarcpy.indent("Field length validation", 2))
    for f in field_list:
        if len(f.name) > 30:
            validation = False
    if validation is False:
        logger.info(ceaarcpy.indent("Validation fail, modifying feature class schema", 2))
        output_location = os.path.dirname(feature_class)
        feature_class_temp = feature_class + "_temp"
        feature_class_describe = arcpy.Describe(feature_class)
        if arcpy.Exists(feature_class_temp):
            arcpy.Delete_management(feature_class_temp)
        arcpy.CreateFeatureclass_management(out_path=output_location,
                                            out_name=os.path.basename(feature_class_temp),
                                            geometry_type=feature_class_describe.shapeType,
                                            spatial_reference=feature_class_describe.spatialReference)
        mod_field = {}
        for f in field_list:
            if f.name not in ["OBJECTID", "Shape", "Shape_Length"]:
                f_length = f.length
                f_name = f.name
                f_type = f.type
                f_precision = f.precision
                f_scale = f.scale
                if len(f_name) > 30:
                    mod_field[f_name] = f_name[:29]
                    f_name = f_name[:29]
                arcpy.AddField_management(in_table=feature_class_temp,
                                          field_name=f_name,
                                          field_type=f_type,
                                          field_length=None if f_length == 0 else f_length,
                                          field_precision=None if f_length == 0 else f_precision,
                                          field_scale=None if f_scale == 0 else f_scale)
        fieldmapping = arcpy.FieldMappings()
        fieldmapping.addTable(feature_class)
        for input_field, output_field in mod_field.items():
            field_map = fieldmapping.getFieldMap(fieldmapping.findFieldMapIndex(input_field))
            temp = field_map.outputField
            temp.name = output_field
            field_map.outputField = temp
            fieldmapping.replaceFieldMap(fieldmapping.findFieldMapIndex(input_field), field_map)
        arcpy.Append_management(feature_class, feature_class_temp, "NO_TEST", fieldmapping)
        arcpy.Delete_management(feature_class)
        arcpy.Rename_management(feature_class_temp, feature_class)

View solution in original post

5 Replies
DanPatterson_Retired
MVP Esteemed Contributor

field name limits for those that aren't sure what that is about 

Define fields in tables—Geodatabases | ArcGIS Desktop 

A lookup table and setting an alias after isn't an option I suppose.

Are the truncated field names causing issues or conflicts or is it just that the overly long names aren't coming through in their entirety ?

0 Kudos
forestknutsen1
MVP Regular Contributor

I am getting a oracle error. The field name length violates the oracle column length.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

shapefile field lengths are even more restrictive than Oracle.  File gdb is 64 chars.

You are kindof stuck I guess

0 Kudos
forestknutsen1
MVP Regular Contributor

Ya, I think the most elegant solution would be to build a new schema in the FGDB and then append. My number 2 option. I was hoping there was some cool esri way to do it..

So, in the interest of time I put the shapefile export/import fix into production, as that is only 15 lines of code or so... If I don't get any user push back I am going to call it good

forestknutsen1
MVP Regular Contributor

People did not like the shapefile solution... So...

def field_validation(feature_class):
    field_list = arcpy.ListFields(feature_class)
    validation = True
    logger.info(ceaarcpy.indent("Field length validation", 2))
    for f in field_list:
        if len(f.name) > 30:
            validation = False
    if validation is False:
        logger.info(ceaarcpy.indent("Validation fail, modifying feature class schema", 2))
        output_location = os.path.dirname(feature_class)
        feature_class_temp = feature_class + "_temp"
        feature_class_describe = arcpy.Describe(feature_class)
        if arcpy.Exists(feature_class_temp):
            arcpy.Delete_management(feature_class_temp)
        arcpy.CreateFeatureclass_management(out_path=output_location,
                                            out_name=os.path.basename(feature_class_temp),
                                            geometry_type=feature_class_describe.shapeType,
                                            spatial_reference=feature_class_describe.spatialReference)
        mod_field = {}
        for f in field_list:
            if f.name not in ["OBJECTID", "Shape", "Shape_Length"]:
                f_length = f.length
                f_name = f.name
                f_type = f.type
                f_precision = f.precision
                f_scale = f.scale
                if len(f_name) > 30:
                    mod_field[f_name] = f_name[:29]
                    f_name = f_name[:29]
                arcpy.AddField_management(in_table=feature_class_temp,
                                          field_name=f_name,
                                          field_type=f_type,
                                          field_length=None if f_length == 0 else f_length,
                                          field_precision=None if f_length == 0 else f_precision,
                                          field_scale=None if f_scale == 0 else f_scale)
        fieldmapping = arcpy.FieldMappings()
        fieldmapping.addTable(feature_class)
        for input_field, output_field in mod_field.items():
            field_map = fieldmapping.getFieldMap(fieldmapping.findFieldMapIndex(input_field))
            temp = field_map.outputField
            temp.name = output_field
            field_map.outputField = temp
            fieldmapping.replaceFieldMap(fieldmapping.findFieldMapIndex(input_field), field_map)
        arcpy.Append_management(feature_class, feature_class_temp, "NO_TEST", fieldmapping)
        arcpy.Delete_management(feature_class)
        arcpy.Rename_management(feature_class_temp, feature_class)