[GIS] Comparing field values of two feature classes using ArcPy

Question

I have an intermediate fc #1 that contains data that I would like to append to my production fc # 2(final output). When working with the data, I would only like to append data from my fc # 1that is not in fc # 2, to avoid duplicates, etc. My field that I would like to check is SRNumber I would like to only append from fc # 1 to fc # 2 when SRNumber is not in fc # 2. Can this be done via ArcPy?

Maybe get values of fields from fc # 1 SRNumber store in list and write anything not in list to fc # 2?

Answer 1

Here is a Python toolbox tool I recently created that will insert all selected records from one fc/table into another fc/table. It does all of the field matching and validates the inputs to make sure the inputs are compatible types with matching geometry and spatial reference when applicable. It acts like copy/paste, except that it also works for StandAlone Tables (something ArcMap does not support when you want to Append from an FC to a Standalone table). It does not support inserts from a Standalone Table to an FC, but it supports all 3 other combinations (FC to FC, FC to Table, and Table to Table). It is very fast.

The original tool just inserted anything selected in the first FC/table, but I have modified it in the code below to add lines to gather all SRNumbers into a dictionary from fc 2 (the insertFC) and validate that any SRNumber in the inserttFC is skipped so that only the records from fc 1 (the FC) not found in the insertFC are inserted. (However, these modification have not been tested and you should first use it on a copy of your data to make sure it works before doing anything with your master data). I have added comments to note what changes I made to the original code just to solve the specific request in this post concerning the SRNumber behavior.

So create a new Python toolbox and insert the code below into it. Then add both fcs to your current map and select every feature in fc 1. Then run the tool and make the first FC your fc 1 and make the insert FC your fc 2. Close any open table view before running the tool, since there is a refresh problem that is Esri's fault (Append tool has the same problem) and after running the tool reopen the table view and you will see the newly inserted features selected. If you have an Editor session going the inserts will disappear without warning if you stop the edit session without saving the edits. But if you save the edits the inserts will be permanent.

import arcpy

class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Field Match Tools"
        self.alias = ""

        # List of tool classes associated with this toolbox
        self.tools = [InsertSelectedFeaturesOrRows]

class InsertSelectedFeaturesOrRows(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Insert Selected Features or Rows"
        self.description = ""
        self.canRunInBackground = False

    def getParameterInfo(self):
        """Define parameter definitions"""
        # First parameter
        param0 = arcpy.Parameter(
            displayName="Input Layer or Table View",
            name="in_prim_table",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")

        # Second parameter
        param1 = arcpy.Parameter(
            displayName="Output Layer or Table View",
            name="out_table",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")

        # Third parameter
        param2 = arcpy.Parameter(
            displayName="Number of Copies to Insert",
            name="number_of_row_copies",
            datatype="GPLong",
            parameterType="Required",
            direction="Input")

        param2.value = 1        

        # Fourth parameter
        param3 = arcpy.Parameter(
            displayName="Derived Layer or Table View",
            name="derived_table",
            datatype="GPTableView",
            parameterType="Derived",
            direction="Output")

        param3.parameterDependencies = [param1.name]
        param3.schema.clone = True

        params = [param0, param1, param2, param3]

        return params

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""
        if parameters[1].value:
            insertFC = parameters[1].value
            strInsertFC = str(insertFC)
            if parameters[0].value and '<geoprocessing Layer object' in strInsertFC:
                FC = parameters[0].value
                strFC = str(FC)
                if not '<geoprocessing Layer object' in strFC:
                    print("Input FC must be a layer if output is a layer")
                    parameters[0].setErrorMessage("Input must be a feature layer if the Output is a feature layer!")
                else:
                    dscFCLyr = arcpy.Describe(FC)
                    dscinsertFCLyr = arcpy.Describe(insertFC)
                    # add the SHAPE@ field if the shapetypes match
                    if dscFCLyr.featureclass.shapetype != dscinsertFCLyr.featureclass.shapetype:
                        print("Input and Output have different geometry types!  Geometry must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same geometry")

                    if dscFCLyr.featureclass.spatialReference.name != dscinsertFCLyr.featureclass.spatialReference.name:
                        print("Input and Output have different Spatial References!  Spatial References must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same Spatial References!  Spatial References must match!")
        if parameters[2].value <= 0:
            parameters[2].setErrorMessage("The Number of Row Copies must be 1 or greater")
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        try:
            mxd = arcpy.mapping.MapDocument(r"CURRENT")
            df = arcpy.mapping.ListDataFrames(mxd)[0]

            FC = parameters[0].value
            insertFC = parameters[1].value

            strFC = str(FC)
            strInsertFC = str(insertFC)

            FCLyr = None
            insertFCLyr = None

            for lyr in arcpy.mapping.ListLayers(mxd, "", df):
                # Try to match to Layer
                if '<geoprocessing Layer object' in strFC:
                    if lyr.name.upper() == FC.name.upper():
                        FCLyr = lyr
                if '<geoprocessing Layer object' in strInsertFC:
                    if lyr.name.upper() == insertFC.name.upper():
                        insertFCLyr = lyr
            if FCLyr == None or insertFCLyr == None:
                # Try to match to table if no layer found
                if FCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strFC.upper():
                            FCLyr = table
                            break
                if insertFCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strInsertFC.upper():
                            insertFCLyr = table
                            break

            # If both layers/tables are found then process fields and insert cursor
            if FCLyr != None and insertFCLyr != None:
                dsc = arcpy.Describe(FCLyr)         

                selection_set = dsc.FIDSet

                # only process layers/tables if there is a selection in the FCLyr
                if len(selection_set) > 0:
                    print("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))
                    arcpy.AddMessage("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))

                    FCfields = arcpy.ListFields(FCLyr)
                    insertFCfields = arcpy.ListFields(insertFCLyr)

                    # Create a field list of fields you want to manipulate and not just copy

                    # Code inserted for this post only.  Remove to use my original code
                    sourceFieldsList = ['SRNumber', 'SRNumber']
                    # Code inserted for this post only.  Remove to use my original code
                    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(insertFCLyr, sourceFieldsList)} 

                    # All of these fields must be in the insertFC    
                    # Code inserted for this post only.  Remove to use my original code
                    manualFields =  ['SRNumber']
                    # Original code in next line.  Remove comment
                    # manualFields =  []
                    matchedFields = []
                    for manualField in manualFields:
                        matchedFields.append(manualField.upper())
                    for FCfield in FCfields:
                        for insertFCfield in insertFCfields:
                            if (FCfield.name.upper() == insertFCfield.name.upper() and
                                FCfield.type == insertFCfield.type and
                                FCfield.type <> 'Geometry' and
                                insertFCfield.editable == True and
                                not (FCfield.name.upper() in matchedFields)):    

                                matchedFields.append(FCfield.name)    
                                break
                            elif (FCfield.type == 'Geometry' and
                                  FCfield.type == insertFCfield.type):

                                matchedFields.append("SHAPE@")
                                break
                            elif insertFCfield.type == "OID":
                                oid_name = insertFCfield.name

                    if len(matchedFields) > 0:
                        # Print the matched fields list
                        print("The matched fields are: {}".format(matchedFields))
                        arcpy.AddMessage("The matched fields are: {}".format(matchedFields))

                        copies = parameters[2].value
                        print("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))
                        arcpy.AddMessage("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))

                        oid_list = []
                        # arcpy.AddMessage(oid_name)
                        dscInsert = arcpy.Describe(insertFCLyr)
                        if '<geoprocessing Layer object' in strInsertFC:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert.dataElement, oid_name)
                        else:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert, oid_name)
                        rowInserter = arcpy.da.InsertCursor(insertFCLyr, matchedFields)
                        print("The output workspace is {}".format(insertFCLyr.workspacePath))
                        arcpy.AddMessage("The output workspace is {}".format(insertFCLyr.workspacePath))
                        if '<geoprocessing Layer object' in strInsertFC:
                            versioned = dscInsert.featureclass.isVersioned
                        else:
                            versioned = dscInsert.table.isVersioned

                        if versioned:
                            print("The output workspace is versioned")
                            arcpy.AddMessage("The output workspace is versioned")
                            with arcpy.da.Editor(insertFCLyr.workspacePath) as edit:
                                with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:       
                                    for row in rows:
                                        # Remove the next line and dedent the two lines below for original code
                                        if not row[0] in valueDict:
                                            for i in range(copies):
                                                oid_list.append(rowInserter.insertRow(row))
                        else:
                            print("The output workspace is not versioned")
                            arcpy.AddMessage("The output workspace is not versioned")
                            with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:       
                                for row in rows:       
                                    # Remove the next line and dedent the two lines below for original code
                                    if not row[0] in valueDict:
                                        for i in range(copies):
                                            oid_list.append(rowInserter.insertRow(row))
                        del row       
                        del rows       
                        del rowInserter  
                        if len(oid_list) == 1:
                            whereclause = oid_name + ' = ' + str(oid_list[0])
                        elif len(oid_list) > 1:
                            whereclause = oid_name + ' IN (' + ','.join(map(str, oid_list)) + ')'
                        if len(oid_list) > 0:
                            # arcpy.AddMessage(whereclause)
                            # Switch feature selection
                            arcpy.SelectLayerByAttribute_management(FCLyr, "CLEAR_SELECTION", "")
                            arcpy.SelectLayerByAttribute_management(insertFCLyr, "NEW_SELECTION", whereclause)
                            print("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                            arcpy.AddMessage("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                    else:
                        print("Input and Output have no matching fields")
                        arcpy.AddMessage("Input and Output have no matching fields")
                else:
                    print("There are no features selected")
                    arcpy.AddMessage("There are no features selected")


            # report if a layer/table cannot be found
            if FCLyr == None:
                print("There is no layer or table named '{}' in the map".format(FC))
                arcpy.AddMessage("There is no layer or table named '" + FC + "'")
            if insertFCLyr == None:
                print("There is no layer or table named '{}' in the map".format(insertFC))
                arcpy.AddMessage("There is no layer or table named '{}' in the map".format(insertFC))

            arcpy.RefreshActiveView()                             
            return
        except Exception as e:     
            # If an error occurred, print line number and error message     
            import traceback, sys     
            tb = sys.exc_info()[2]     
            print("Line %i" % tb.tb_lineno)
            arcpy.AddMessage("Line %i" % tb.tb_lineno)
            print(e.message)
            arcpy.AddMessage(e.message)