Trying to use fieldmap to append external featureclass/shapefile to new featureclass using external table for mapping

Question

I am trying to work out a way to populate a series of shape files into a new set of Datasets and feature classes.

I have an external table of what the destination feature class field name should be and its corresponding source feature class/shape field name.

Assumptions: Destination feature class field order is not the same as the source field order (but the external table tells me the name at least)

Destination feature class field name is not necessarily the same as the source field name (but again the external table tells me what the equivalent map is)

I iterate through my destination database by dataset/feature class, then for each feature class I iterate through the fields. Then for each field, i match that dataset/featureclass/field combo on my external table (using a temporary table) to find what the equivalent source featureclass/field is.

Finally i try to build a field map of the appropriate fieldmappings in order to run an append command to populate the destination featureclass.

I am the first to admit I get ideas from lots of other people's code so learn on the fly and comment a lot in my code to try and understand what the script is doing so apologies in advance if it looks like a dogs breakfast but it is how I am trying to learn.

I am still getting a number of errors (i can't seem to reset the field maps for the next feature class for instance but i am not worried about that at the moment) but the main thing I am trying to work out is how to build the field mappings and am getting monumentally confused.

So far I Have gotten this far:

import arcpy
import os
arcpy.env.workspace = r"C:1994円 Schema.gdb"
SourceDataset = r"C:\GDA94"
DestinationDatabase = r"C:1994円 Schema.gdb"
xlsx = r"C:\Documents\ShapeFileToFeatureClassTableMap.xlsx"
TempTable = r"C:\Temp\ShapeFileToFeatureClassMap.dbf"
#delete the temp table if it is there still
if arcpy.Exists(TempTable):
 arcpy.Delete_management(TempTable)
 
SourceFeatureClass = "" 
fieldmappings = arcpy.FieldMappings()
DatasetList = arcpy.ListDatasets(wild_card = "*" , feature_type = "Feature")
arcpy.ExcelToTable_conversion(xlsx,TempTable,Sheet="Sheet1")
#iterate through each dataset
for DS in DatasetList:
 FeatureClassList = arcpy.ListFeatureClasses(wild_card = "*" , feature_type = "All", feature_dataset = DS)
 
 #iterate through each featureclass
 for FC in FeatureClassList:
 print ("DS: ",DS,", FC: ",FC) 
 #create the field list for the specific feature class we are dealing with
 FieldsInDestinationFeatureClass = [f for f in arcpy.ListFields(FC) if f.name not in ("Shape", "OBJECTID", "Shape_Length" , "Shape_Area")]
 
 for SpecificField in FieldsInDestinationFeatureClass:
 print (SpecificField.name)
 #run the fieldmap commands (I think)
 MyFieldMap = arcpy.FieldMap()
 MyFieldMap.addInputField(FC,SpecificField.name)
 
 
 
 #look through my mapping table to find a match
 with arcpy.da.SearchCursor(TempTable,["*"]) as AllRowsInTempTable:
 for row in AllRowsInTempTable:
 #row[0] dbf row counter (starts at 0 for first row)
 #row[1] TargetDataset
 #row[2] TargetFeatureclass
 #row[3] TargetFieldName
 #row[4] SourceFeatureclass
 #row[5] SourceFeatureClassFieldName
 #found a match (dataset, feature class and field name)
 if row[1] == DS and row[2] == FC and SpecificField.name == row[3]:
 print ("matchedrow is: ",row[0]) #matched row in the mapping table (note this is out by 2 as it appears to exclude headers and starts row counter at 0
 if os.path.join(SourceDataset,row[4]) <> SourceFeatureClass:
 print "Needing to change the source location to match what it should be"
 SourceFeatureClass = os.path.join(SourceDataset,row[4])
 print SourceFeatureClass
 MyFieldMap.addInputField(SourceFeatureClass,row[5]) #suspect this is causing issues?
 #add everything into the field mappings
 fieldmappings.addFieldMap(MyFieldMap)
 #print out the field mappings to see what is happening
 Fieldmapstring = fieldmappings.exportToString()
 print Fieldmapstring
 
 
 print ("running append on target datset: ",FC)
 arcpy.Append_management(SourceFeatureClass, FC, schema_type = "NO_TEST", field_mapping=fieldmappings)
 print "reset field mappings"
 fieldmappings.removeAll()

If i look how the field map builds after it has completed the construction, it comes out like this

ROW_ID "RowID" true true false 5 Text 0 0 ,First,#,C:1994円 Schema.gdb\Cemetery_Text,ROW_ID,-1,-1,C:\GDA94\Cemetery\Cemetery_Text.shp,ROW,-1,-1;AMENDATE "AmmendmentDate" true true false 8 Double 0 0 ,First,#,C:1994円 Schema.gdb\Cemetery_Text,AMENDATE,-1,-1,C:\GDA94\Cemetery\Cemetery_Text.shp,AMENDATE,-1,-1;PREVDATE "PreviousDate" true true false 8 Double 0 0 ,First,#,C:1994円 Schema.gdb\Cemetery_Text,PREVDATE,-1,-1,C:\GDA94\Cemetery\Cemetery_Text.shp,PREVDATE,-1,-1

However if i run the append inside Arc catalogue and see how it construct its own NO_TEST mapping, it looks like this:

ROW_ID "RowID" true true false 5 Text 0 0 ,First,#,C:\GDA94\Cemetery\Cemetery_Text.shp,ROW,-1,-1;AMENDATE "AmmendmentDate" true true false 8 Double 0 0 ,First,#,C:\GDA94\Cemetery\Cemetery_Text.shp,AMENDATE,-1,-1;PREVDATE "PreviousDate" true true false 8 Double 0 0 ,First,#,C:\GDA94\Cemetery\Cemetery_Text.shp,PREVDATE,-1,-1

So i can see with that first mapping: my generated map is different to the arc catalogue version

Arc catalogue version:

ROW_ID "RowID" true true false 5 Text 0 0 ,First,#,C:\GDA94\Cemetery\Cemetery_Text.shp,ROW,-1,-1;

My generated version:

ROW_ID "RowID" true true false 5 Text 0 0 ,First,#,C:1994円 Schema.gdb\Cemetery_Text,ROW_ID,-1,-1,C:\GDA94\Cemetery\Cemetery_Text.shp,ROW,-1,-1;

It seems to work ok up to the hashtag (which all looks like destination field property information), but then after the hash, i have both my destination featureclass path and the source featureclass path whereas the generated one doesn't.

I am now wondering if i should build the fieldmap as a text string if i know what each of the comma separations are? I have tried experimenting with .outputField but that affects the first bit of code rather than that final bit. Kind of running out of ideas trying to work out how to generate this field map and actually understand what it is doing.

Excel Mapping sheet has the following columns:

DatasetTarget | FeatureClassTarget | FeatureClassTargetField | FeatureClassSource | FeatureClassSourceField FeatureClassmappingTable

• Dami Barnes: sorry i was following the guideline where it said to provide as much information to make it clear as possible. i didn't know how to explain it all.... Single shape file (or featureclass) (source) to single featureclass (destination). Field names of source aren't guaranteed to be in the same order or the same name as the destination field names. But mapping spreadsheet (excel) tells me what should go from the one source field to the one destination field. (if it isn't on the spreadsheet, it doesn't need to be mapped). Trying fieldmap but maybe that's the wrong approach?

Answer 1

I don't know if my answer will help you at all, but I'll offer you my "solution" to field mapping... I routinely download address points (and many other layers) from multiple different counties, and combine/merge them together to make a set of "Regional Address Points". Of course, each county has a different field schema and so if you do a simple merge, you'll get things like having a HOUSE_NUM field and a HOUSENUM field.

So, using python, I "process" each set of Address Points and export them to a "temporary" feature class/shapefile, and part of that process is changing all the field names to a common schema so when I merge them all together in the end, the field names and field order will be exactly how I want them. I'll (try to) explain the process of changing the field names/order here...

def MainScript():
 # Create a FeatureLayer for the Original set of data
 arcpy.MakeFeatureLayer_management("C:/MyOriginalData/FranklinCountyAddressPoints.shp", "lyr_AddressPoints")
 # ----
 # Do any needed calculations/adding fields/etc here before we export
 # ----
 # Export the data to a "temporary" shapefile with the desired field names/order
 fcFieldMaps = GetFieldMaps("AddressPoints")
 inputFC = "lyr_AddressPoints"
 outputFC_Path = "C:/MyTemporaryData"
 outputFC_Name = "FranklinCountyAddressPointsTemp.shp"
 outputFC_WhereClause = ""
 fieldmappings = GetFieldMappings(inputFC, fcFieldMaps)
 arcpy.FeatureClassToFeatureClass_conversion(inputFC, outputFC_Path, outputFC_Name, outputFC_WhereClause, fieldmappings, "")
def GetFieldMaps(layer):
 # This list sets the desired field names and order
 # First column is the original field Name
 # Second, third, and fourth columns are the desired field Name, Type, and Length
 # Comment out the line if that particular field doesn't exist in the original data
 fcFieldMaps = []
 if layer == "AddressPoints":
 fcFieldMaps.append(["HOUSE_NUM" , "ADDR_NUM" , "Long" , 0])
 fcFieldMaps.append(["PRE_DIR" , "PRE_DIR" , "String" , 2])
 fcFieldMaps.append(["STR_NAME" , "STR_NAME" , "String" , 30])
 fcFieldMaps.append(["STR_TYPE" , "STR_TYPE" , "String" , 4])
 fcFieldMaps.append(["SUF_DIR" , "SUF_DIR" , "String" , 2])
# fcFieldMaps.append(["SUF_DIR2" , "SUF_DIR2" , "String" , 2])
 fcFieldMaps.append(["UNIT_TYPE" , "UNIT_TYPE" , "String" , 4])
 fcFieldMaps.append(["UNIT_NUM" , "UNIT_NUM" , "String" , 10])
 fcFieldMaps.append(["LSN" , "LSN" , "String" , 64])
# fcFieldMaps.append(["ALIAS" , "ALIAS" , "String" , 64])
 fcFieldMaps.append(["MUNIC" , "CITY" , "String" , 30])
 fcFieldMaps.append(["USPS_CITY" , "POSTALCITY" , "String" , 30])
 fcFieldMaps.append(["STATE" , "STATE" , "String" , 2])
 fcFieldMaps.append(["ZIP" , "ZIP" , "String" , 5])
 fcFieldMaps.append(["COUNTY" , "COUNTY" , "String" , 3])
 
 if layer == "Centerlines":
 fcFieldMaps.append(["STR_PRE" , "STR_PRE" , "String" , 2])
 fcFieldMaps.append(["STR_NAME" , "STR_NAME" , "String" , 50])
 fcFieldMaps.append(["STR_TYPE" , "STR_TYPE" , "String" , 4])
 fcFieldMaps.append(["STR_SUFFIX" , "STR_SUFFIX" , "String" , 2])
# fcFieldMaps.append(["STR_SUFIX2" , "STR_SUFIX2" , "String" , 2])
 fcFieldMaps.append(["L_ADD_FROM" , "L_ADD_FROM" , "Long" , 0])
 fcFieldMaps.append(["L_ADD_TO" , "L_ADD_TO" , "Long" , 0])
 fcFieldMaps.append(["R_ADD_FROM" , "R_ADD_FROM" , "Long" , 0])
 fcFieldMaps.append(["R_ADD_TO" , "R_ADD_TO" , "Long" , 0])
 fcFieldMaps.append(["LSN" , "LSN" , "String" , 64])
 fcFieldMaps.append(["ALIAS" , "ALIAS" , "String" , 50])
 fcFieldMaps.append(["L_COMM" , "L_CITY" , "String" , 30])
 fcFieldMaps.append(["R_COMM" , "R_CITY" , "String" , 30])
 fcFieldMaps.append(["L_STATE" , "L_STATE" , "String" , 2])
 fcFieldMaps.append(["R_STATE" , "R_STATE" , "String" , 2])
 fcFieldMaps.append(["L_ZIP" , "L_ZIP" , "String" , 5])
 fcFieldMaps.append(["R_ZIP" , "R_ZIP" , "String" , 5])
def GetFieldMappings(InputFeatureClass, FieldMapsList):
 # This function returns a FieldMappings object for ONE input feature class
 # Note that it sets the Name and AliasName to the same value
 # Also note that it sets the IsNullable property to False
 try:
 fieldmappings = arcpy.FieldMappings()
 for field in FieldMapsList:
 fldmap = arcpy.FieldMap()
 fldmap.addInputField(InputFeatureClass, field[0])
 fld = fldmap.outputField
 fld.name = field[1]
 fld.aliasName = field[1]
 fld.type = field[2]
 fld.length = field[3]
 fld.isNullable = False
 fldmap.outputField = fld
 fieldmappings.addFieldMap(fldmap)
 return fieldmappings
 except Exception, err:
 print(str(err)) 
if __name__ == '__main__':
 MainScript()

In the above code, we start by making creating a FeatureLayer for the original data, which allows us to make any needed adjustments to the data before exporting

Then we get the "FieldMaps" (desired field names and order of the fields) Note that the code will change the HOUSE_NUM field to ADDR_NUM, the MUNIC field to CITY, and the USPS_CITY field to POSTALCITY. I don't have a SUF_DIR2 field or an ALIAS field, so I just comment those lines out so they're not processed.

After that, it sets the input and output names, and then creates the "FieldMappings"... Then it creates the "temporary" shapefile...

Then in a separate script, you can merge all the "temporary files" together, something like this...

# Make FeatureLayers from the all the Temp feature classes")
arcpy.MakeFeatureLayer_management("C:/MyTemporaryData/FranklinCountyAddressPointsTemp.shp", "lyr_Franklin")
arcpy.MakeFeatureLayer_management("C:/MyTemporaryData/DelawareCountyAddressPointsTemp.shp", "lyr_Delaware")
arcpy.MakeFeatureLayer_management("C:/MyTemporaryData/LickingCountyAddressPointsTemp.shp", "lyr_Licking")
# Merge the layers
inputFC = ["lyr_Franklin", "lyr_Delaware", "lyr_Licking"]
outputFC = "C:/MyFinalData/RegionalAddressPoints.shp"
arcpy.Merge_management(inputFC, outputFC)

Hopefully this all makes sense. Note that for the first part of the code above, you will have 1 script file PER shapefile/featureclass. Basically make a "template" copy, then make a copy for each shapefile, and adjust each one as needed for that particular shapefile. As you can see, this script could also be a template for a "Centerlines" layer. I hope this is (somewhat) helpful, as I need more internet points... ;)

Answer 2

I didn't really manage to work out field mappings entirely, but someoene did post an insert cursor solution that I experimented with and managed to get it to work. Thank you to that mysterious person who looks like they have disappeared.

So far this seems to be working (I think)

the external mapping sheet looks something like this: enter image description here

and the code that loops through the target geodatabase is thus:

DatasetList = arcpy.ListDatasets () 
#iterate through each of the datasets
for DS in DatasetList:
 FeatureClassList = arcpy.ListFeatureClasses(wild_card = "*" , feature_dataset = DS)
 #iterate through each of the feature clases inside of the dataset
 for FC in FeatureClassList:
 print ("DS: ",DS,", FC: ",FC)
 #used later on to compare the source Feature reference to this target feature reference (which should all be GDA2020) 
 TargetSpatialReference = arcpy.Describe(FC).spatialReference
 #print TargetSpatialReference.exportToString()
 #Build a where_clause for creating the NumPyArray with only the entries from the Excel spreadsheet (converted to a table)
 #that are relevant to the target fields in the target feature class. 
 #Ie for my target feature class, see if there are any field mappings that were specified from my excel spreadsheet
 #see post courtesy of blah238 from
 #https://gis.stackexchange.com/questions/27457/including-variable-in-where-clause-of-arcpy-select-analysis
 TheWhereClause = buildWhereClause(TempLookupTable,"TrgtDS",DS) + " AND " + buildWhereClause(TempLookupTable,"TrgtFC",FC)
 #print TheWhereClause
 #create the NumPy Array ( i only need the source location, the source feature class and the source field name to map to my target field name
 tbl = arcpy.da.TableToNumPyArray(TempLookupTable,('SrceLoc','SrceFC','SrceFldNm','TrgtFldNm'),where_clause = TheWhereClause )
 #print tbl
 #check if there were indeed any fields in the mapping spreadsheet for this particular Target DS/FC mix
 #if the above created filtered numPy array contained no entries, then skip all of this bit
 if tbl.size <> 0: #if there is nothing in the mapping file to tell us what to do, don't do any more
 #create the source feature class path
 for line in tbl:
 print line[0]
 #get the os path based on the source dataset name from the first line in the numpy array
 #the source name is one of three sources
 SourceDataset = SourceDatasetPath(line[0])
 print ("SourceDataset: ", SourceDataset)
 #complete the os Path by adding the feature class from the source also 
 SourceFC = os.path.join(SourceDataset,line[1])
 print ("SourceFeatureClass: ",SourceFC)
 #break #generated the source featureclass path so can exit out of the loop,
 #SourceFeatureClass and TargetFeatureClass is assumed to be one to one mapping
 #consider using the tartget field name as an additional check between the NumPy array and the target DS/FC/field
 #create the Target featureclass path that we are currently iterated to through the DS, FC loops
 TargetFC = os.path.join (TargetDatabase,DS,FC)
 print ("TargetFC: ",TargetFC)
 SourceFields = tbl['SrceFldNm']
 TargetFields = tbl['TrgtFldNm']
 #find out what spatial reference the source feature class is in
 SourceSpatialReference = arcpy.Describe(SourceFC).spatialReference
 #print SourceSpatialReference.exportToString()
 #create a scratch shape file to do a projection to the target spatial reference system if the source coordinate system is not the same as the Target
 if SourceSpatialReference <> TargetSpatialReference:
 
 #generate a transformation List if there is a difference 
 SourcetoTargetTransformation = arcpy.ListTransformations(SourceSpatialReference,TargetSpatialReference)
 #note i create the transformed scratch file in the same location as the source but this might not work if one of the sources is the server geodatabase
 #consider creating the scratch just in a safe offline location of some descriptor for all source scenarios
 Scratch94to2020 = os.path.join(SourceDataset,"scratch94-2020.shp")
 DeleteStuffIfRequired(Scratch94to2020) #just in case it existed before creating it based on the path created above
 #undertake the projection from the source to the scratch location which should now be in the same spatial reference as what the target is
 arcpy.Project_management(SourceFC, Scratch94to2020, TargetSpatialReference, SourcetoTargetTransformation, SourceSpatialReference)
 #refer to the scratch shape file to do the copy insert process rather than the original shape file that was in a different spatial reference
 #the scrathc file should now be in the same spatial reference as the target FC
 SourceFC = Scratch94to2020
 
 #iterate through the target featureclass in the fields recognised from the mapping xlsspreadsheet
 curT = arcpy.da.InsertCursor(TargetFC,TargetFields) # note arcmap 10.6 doesn't allow trspatial coordinate referencing in this step, arcgis pro does seem to allow it though
 
 #grab the row information from the source feature class and the equivalent mapped source field (that should line up to the target fields)
 with arcpy.da.SearchCursor(SourceFC,SourceFields) as cursor:
 for row in cursor:
 curT.insertRow(row)
 del curT
 #clean up and remove the scratch ready for the next loop around
 DeleteStuffIfRequired(Scratch94to2020)
 
 #scenario if there was nothing in the NumPy array (skip over that particular target Dataset/Featureclass combo
 elif tbl.size == 0:
 print ("Nothing to do mapping for DS: ",DS,", FC: ",FC)

The code loops through the target dataset/featureclass combo.

Then for each featureclass. It generates a NumPy array (haven't fully worked out how they work compared to normal tables but it does so i haven't questioned it) from the mappings sheet filtered on that dataset/featureclass.

If there are mappings to be done then it works out the source featureclass location from the mapping table

There can then be a list of source fields and target fields It can then do an insert cursor on the target fields and iterate through the source fields in the source feature class an put them in the target feature class.

One hiccup I didn't realise was a projection of the spatial reference from the source to the target but i check both of those and if they are different, then run a projection over the source (and save to a temporary shape file) and then the source becomes that temporary shape file instead to do the insert cursor on.

the SourceDatasetPath definition just returns the known os path join for the source featureclass based on the source hint in the xls sheet

posted here in case this might be of some use to someone else but also thanks to the mysterious person who steered me away from the field mapping and to the insert cursor with NumpyArray instead (which means I still haven't worked out field mappings but i will delve a little deeper into that and take another attempt at it still struggling even with Jason Miller's example )