Copying dbf data into shapefile using ArcPy?

Question

How do I perform a one-to-many join in a python script, from a dbf to a shapefile?

I saw (Greg Krakow's method) using QGIS, but I can't use it (I'm working on it, but QGIS is not yet recognize as an "official and usable" option within our organisation).

My dbf contains many records with the same ID and my source shapefile contains the geometry for each ID.

My approach is to create from scratch a shapefile ("outFile" in the script) by copying the structure of my dbf (adding field by field), using arcpy.FieldList()

dbfFieldList = arcpy.ListFields(dbf)

for field in dbfFieldList:
    fieldTypeShp = validFieldType(field.type)
    arcpy.AddField_management (outFile, field.name, fieldTypeShp, "", "", field.length)

validFieldType(field.type) is a function "converting" the field type of my dbf to a valid field type for arcpy. For example, I have a "smallint" which came back as "SHORT" so I could use AddField_management...

I figured that if I could read my dbf line by line, I could, field by field (since both my dbf and shp have the exact same structure), udpate the value of my shapefile.

#-- Seach cursor for my .dbf
searchCur = arcpy.SearchCursor(dbf)

for sCur in searchCur:

    #-- Insert cursor for my shapefile
    cur = arcpy.InsertCursor(outFile)
    row = cur.newRow()

    #-- Reading for the current row, the fields value, one by one.
    for field in dbfFieldList:
        fieldName = field.name
        val = sCur.getValue(fieldName)
        row.setValue(fieldName,val)

    #-- Inserting the new row into my shapefile
    cur.InsertRow(row) 

But I have an error executing the function "setValue". If I remove that line and the cur.InsertRow (or put a "print val" it works perfectly. I have created a shapefile with the same number of row as my dbf, but I can't add the field values.

After that part, I would use the ID field to retrieve the geometry from my source shapefile with the same ID, recreating my 1-to-many join.

If only I could make that part work.

Answer 1

You should be able to overcome the problem by adding in some error handling. Without the exact error message, I can only assume that you are running into issues with attempting to manually fill restricted fields such as OID, as @Jason pointed out. Try the following and see if this works out for you:

dbfFieldList = arcpy.ListFields(dbf)

for field in dbfFieldList:
    fieldTypeShp = validFieldType(field.type)
    try:
        arcpy.AddField_management (outFile, field.name, fieldTypeShp, "", "", field.length)
    except:
        print "Field, '" + field.name + "' cannot be added."

searchCur = arcpy.SearchCursor(dbf)
cur = arcpy.InsertCursor(outFile)

for sCur in searchCur:


    #-- Create a new row in the outfile
    row = cur.newRow()

    #-- Reading for the current row, the fields value, one by one.
    for field in dbfFieldList:
        val = sCur.getValue(field.name)
        try:
            row.setValue(field.name,val)
        except:
            print "Cannot populate field, '" + field.name + "'."

    #-- Inserting the new row into my shapefile
    cur.insertRow(row) 

Answer 2

Assuming you want to create duplicate geometries for those features in the shapefile for which there exist multiple rows having the same ID in the DBF table (one to many relationship), you could do this using Make Query Table, followed by Copy Features to make a permanent feature class or shapefile.

See the example 2 of Examples of queries with the Make Query Table tool for a relevant example:

_{(source: arcgis.com)}

You could implement this pretty easily in ModelBuilder.

You could also do this once interactively using ArcToolbox and then right-click each of the results in the Results window and Copy As Python Snippet to get the syntax and paste it into a new Python script (you'll need to add import arcpy at the top and add arcpy. in front of the geoprocessing function calls).

Note: You will need to first copy the shapefile and DBF file into an intermediate geodatabase as Make Query Table only operates on data from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase, or an OLE DB connection, and all input tables must be in the same workspace.

You can accomplish this intermediate step using additional geoprocessing tools as part of your script or model. You might even be able to use the in-memory workspace for this for better performance and to avoid an intermediate file geodatabase, though I wouldn't suggest this if your shapefile or table are very large.

Answer 3

Finally, it worked! Only, it's **ing slow. I have about a record/second (adding all the field values + find the geometry and adding it to my record), but 5807 record to to. So it took about 2h to run. But with your help, I've done a one-to-many join in arcpy so... Here's the code (sorry for the comments in French. I'll come back to edit my answer to translate them when I'll have the chance :

# -*- coding: utf-8 -*-
###############################################################################
from sys import argv
import arcpy
import os, os.path
###############################################################################

#-- Le dbf contient des types de champs qui ne sont pas compatibles avec les shapefiles.
#-- Pour s'en assurer, on prend le type de champ du dbf et on le convertis en qqchose de compatible

def validFieldType(fieldType):
    if fieldType == "OID":
        return "LONG"
    elif fieldType == "String":
        return "TEXT"
    elif fieldType == "Integer":
        return "LONG"
    elif fieldType == "Double":
        return "DOUBLE"
    elif fieldType == "SmallInteger":
        return "SHORT"
    else:
        return "TEXT"   # On peut entrer presque n'importe quoi en texte alors dans le doute...

#-- Reçoit un nom de fichier, un champ dans lequel rechercher et un ID à trouver.
#-- Retourne la géométrie

#script, dbf, shp = argv

dbf = "D:/#PROJETS_AMT/#RABATTEMENT/2012/Donnees/CITLA_2012au/__Competitivite.dbf"
dbfIdFld = "ID"
shp = "D:/#PROJETS_AMT/#RABATTEMENT/2012/Donnees/CITLA_2012au/__CITLA_2012au_lignes.dbf"
shpIdFld = "To_ID"
arcpy.env.workspace = "D:/#PROJETS_AMT/#RABATTEMENT/2012/Donnees/CITLA_2012au/"

#-- On permet l'overwrite de l'output pour que si ça plante, on ait juste à repartir
#-- le script et que ça réécrire par-dessus les fichiers déjà créés.
arcpy.env.overwriteOutput


#-- On fait la liste des champs du dbf qui va nous servir à recréer un shapefile identique.
dbfFieldList = arcpy.ListFields(dbf)

#-- On va chercher la géométrie du shapefile pour savoir quel type de géométrie créer
descSHP = arcpy.Describe(shp)
descDBF = arcpy.Describe(dbf)
dbfName = descDBF.name[0:-4]
shpName = descSHP.name[0:-4]



#-- J'aime bien mettre des "__" au début de mes outputs pour les retrouver facilement
#-- dans mes dossiers, mais en mergeant des noms de fichiers, parfois ça fait trop long
#-- C'est juste pour éviter d'avoir des "___" dans mes noms de fichiers.
outFile = dbfName + "_" + shpName + ".shp"
outFile = outFile.replace("___","__")

#-- On permet l'overwrite de l'output pour que si ça plante, on ait juste à repartir
#-- le script et que ça réécrire par-dessus les fichiers déjà créés.
listOutput = arcpy.ListFiles("*.shp")

for output in listOutput:
    if output == outFile:
        print "Fichier existant... suppression!"
        arcpy.Delete_management(outFile)


#-- Création du shapefile de sortie
arcpy.CreateFeatureclass_management(arcpy.env.workspace, outFile, descSHP.shapeType, "", "", "", descSHP.spatialReference, "", "", "", "")

#-- On rajoute les mêmes fichiers que le dbf
for field in dbfFieldList:
    if field.name == "ID":
        arcpy.DeleteField_management(outFile, "ID") #-- Un shp "vierge" contient déjà un champ ID. On veut le remplacer par le nôtre.
    fieldTypeShp = validFieldType(field.type)
    arcpy.AddField_management (outFile, field.name, fieldTypeShp, "", "", field.length, "", "", "", "")


#-- On ajoute dans le fichier le même
searchCur = arcpy.SearchCursor(dbf)

#-- Pour suivre la progression
total = arcpy.GetCount_management(dbf)
i = 1

for sCur in searchCur:

    print "Traitement de la ligne %i/%i" %(i, total)

    #-- On ajoute une nouvelle ligne dans le shapefile de sortie
    cur = arcpy.InsertCursor(outFile)
    row = cur.newRow()

    #-- On passe champ par champ pour copier les valeurs

    for field in dbfFieldList:
            fieldName = field.name
            val = sCur.getValue(fieldName)
            try:
                row.setValue(field.name,val)
            except:
                #print "  Cannot populate field %s. Moving on!" %(str(field.name))

    #-- Maintenant on cherche dans le fichier à joindre la géométrie correspondant à l'ID.
    srcCur = arcpy.SearchCursor(shp)

    for src in srcCur:
        if src.getValue(shpIdFld) == sCur.getValue(dbfIdFld):
            row.Shape = src.Shape

    cur.insertRow(row)    
    del cur, row, src, srcCur

    i+=1        
del sCur, searchCur

There's still some hardcoded value, but I'm sure you'll forgive me.

Now, I guess I could have done it with a LEFT JOIN in PostGIS? Would have been easier, no?

Thanks!!