2

I have been tasked with bringing in a dataset from one table with a hard and fast schema into another table with the same. Both tables have very similar fields, but the names and capitalization are slightly different across 133 fields in each table. I have tried using the arcpy.FieldMappings() object and it evades me. I have done several online searches and I managed to find couple of code pieces that almost get me there, but my code still refuses to do the append. The problem appears to be that the fields are shifted in the list and the mappings aren't picking up the proper cross referencing. Particularly, the code fails with one of these two error messages, depending on which variables I am feeding to the fMap object.

AttributeError: 'FieldMap' object has no attribute 'addOutputField'
NameError: The attribute 'outputField' is not supported on this instance of FieldMap.

I do have a cross-reference table built into my GDB that does the field matching. It explicitly lists each input field (in_raw_table) and matches that field with an output field in the output table (in_table).

Here is the code I am using. Quite a lot of it was sourced from an online GIS blog that I found on this subject.

import arcpy
import sys
from arcpy importenv
inPGDB = 'U:/Aquadat/Aquadat.gdb'
arcpy.env.workspace = inPGDB
arcpy.env.OverwriteOutput = True
in_table = 'AquaDat_Incoming'
in_raw_table = 'AquadatRawTable'
xrefTbl = 'AquadatXrefTable_Incoming'
fields = ['SOURCEFIELD_XLS','TARGETFIELD']


def importFromExcel():
    arcpy.env.workspace = inPGDB
    print "Incoming Excel file: {0} \nIncoming Excel sheet {1}".format(inExcel,inSheet)
    arcpy.ExcelToTable_conversion(inExcel,in_table,inSheet)
    print "{0} raw table created".format(in_raw_table)

# define function to get unique field values from a table
def GetUniqueFieldValues(table, field):
    """
    Retrieves and prints a list of unique values in a user-specified field

    Args:
        table (str): path or name of a feature class, layer, table, or table view
        field (str): name of the field for which the user wants unique values

    Returns:
        uniqueValues (list): a list of the unique values in the field
    """
    # get the values
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

# get a list of unique input feature classes
print "Listing the unique input fields."
inputs = GetUniqueFieldValues(xrefTbl, 'SOURCEFIELD_XLS')

# get a list of unique output fields 
print "Listing the unique output fields."
outputFields = GetUniqueFieldValues(xrefTbl, 'TARGETFIELD')

# create an empty field mappings object
print "Creating an empty field mappings object."
fm = arcpy.FieldMappings()

# build the field mappings object
print "Building the field mappings object."
for f in outputFields:
    print "\t" + str(f) + "..."
    # create a field map object
    print "\t...Creating a field map object."
    fMap = arcpy.FieldMap()
    with arcpy.da.SearchCursor(xrefTbl, fields, """{0} = '{1}'""".format('SOURCEFIELD_XLS', f)) as cursor:
        for row in cursor:
            name = row[0]
            nameStr = str(name)
            # add the input field to the field map
            print "\t...Adding " + nameStr + " as an input field."
            fMap.addInputField(in_raw_table, name)
    # set the output name
    print "\t...Setting the output name."
    outputFieldName = fMap.outputField
    outputFieldName.name = f
    fMap.outputField = outputFieldName

    # add the field map to the field mappings object
    print "\t...Adding the field map to the field mappings object."
    fm.addFieldMap(fMap)

print "\nAppending!"
arcpy.Append_management(in_raw_table, in_table, "NO_TEST", fm)

I guess the long and short of it is that I have no idea how to use this thing and could use some help.

  • See my answer to Can the field mapping be shortened in ArcToolbox tools? – Midavalo Jan 30 '17 at 17:29
  • Okay, now that gets me somewhere. The only sticking point is that I have 133 fields in each table to map. – Ken Gaines Jan 30 '17 at 17:38
  • I generally query the columns in the order of the target database, since queries are inherently unordered. Case flexibility might me a sticking point, since ArcGIS doesn't generally honor mixed case in column names. – Vince Jan 30 '17 at 17:55
  • 1
    Unfortunately I know you are correct. The data originators pretty much refuse to change their output field names (which are sent to me in an Excel table, by the way). The temptation to just bring the data into the GDB and forcibly rename the fields to our required output format is overwhelming. I do have to say that your code chunk definitely holds promise. it is MUCH more adaptable to what I am doing. I sincerely thank you for pointing me in that direction. – Ken Gaines Jan 30 '17 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.