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?
2 Answers 2
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)
As @PolyGeo mentioned, a search cursor proceeded by an insert cursor will do the trick here. First nab all the values from fc3 with a little python list comprehension and search cursor. Then iterate through the rows in fc2. Check the values in your check field against your list. If the value isn't in your list, insert the fc2 row into fc3.
The script assumes all the fields are exactly the same, and in the same order between the two feature classes, and that you're working with 10.1 or later.
import arcpy
#input feature class
fc2 = r"C:\temp\temp.gdb\fc2"
#target feature class
fc3 = r"C:\temp\temp.gdb\fc3"
#check field
checkField = "SRNumber"
#Get list of values in field from target feature class
checkValues = set ([r[0] for r in arcpy.da.SearchCursor (fc3, checkField)])
#Get list of fields
fields = [f.name for f in arcpy.ListFields (fc2)]
#Get index of check field
index = fields.index (checkField)
#Create insert cursor for fc3 to allow appending of rows
inCursor = arcpy.da.InsertCursor (fc3, fields)
#Create search cursor to iterate input feature class
cursor = arcpy.da.SearchCursor (fc2, fields)
#iterate
for row in cursor:
#Get value to check
checkValue = row[index]
#Check if value is in target fc
if checkValue in checkValues:
#skip if value is in target fc
continue
#Insert row otherwise
inCursor.insertRow(row)
#Clean up
del cursor
del inCursor
Untested - there may be a spelling mistake or two or three. Happy Pythoning!
SRNumberto a list that you then test usingnot insounds like a good way to go.