[GIS] Joining .csv to attribute table in ArcMap

Question

Is there an ArcMap 10.5 method of joining an excel file (.csv) to a shapefile/attribute table using several fields instead of an explicit identifier?

I've been having trouble with this, and joins simply appends it and fills all rows in the attribute table with the first row from excel.

They don't share a specific number ID, but both have columns (fields?) such as "Township", "Lot", and "Concession" that share the same data. I am looking to join it using these as identifiers and then fill the remaining columns with the excel data.

In summary, is there a way to simply fill the fields in the attribute table with the data from excel?

Answer 1

Add a field to the attribute table and calculate it as Township+Lot+Concession using Field Calculator, do the same in the csv and then join on this field.

Or if you want to complicate things you can also use some Python code. I create a dictionary from the csv file and use this to update fields in the attribute table. You will need to adjust the indexing to match the columns in the csv file, see comment below:

import arcpy

csvfile = r'C:\Test\csvfile.csv' #Change to match your data
fc = r'C:\filegeodatabase.gdb\feature_class' #Change to match your data

d = {} #Create empty dictionary

#Read the lines of the csvfile and and to dictionary
with open(csvfile,'r') as f:
    f.readline() # skip header line
    for line in f:
        row=line.split(',') #Change , to what delimits the fields
        #You might need to adjust indexing on the following row to match the columns of the csv file:
        d[row[0]+row[1]+row[2]]=[row[3],row[4],row[5]]

with arcpy.da.UpdateCursor(fc,['TOWNSHIP','CONCESSION','LOT','LAST_NAME','FIRST_NAME','PPA']) as cursor:
    for row in cursor:
        try:
            row[3],row[4],row[5]=d[row[0]+row[1]+row[2]]
            cursor.updateRow(row)
        except:
            print '{0} {1} {2} was not found in the csvfile'.format(*list(row[3],row[4],row[5]))

Example of indexing: