1

enter image description hereIs 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?

Improve this question
7
  • The short answer is no. The long answer is "concatenate"
    – Ben S Nadler
    Nov 11, 2017 at 6:58
  • Add a field to the attribute table and calculate it as Township+Lot+Concession, do the same in the csv and then join on this field.
    – BERA
    Nov 11, 2017 at 8:47
  • @BERA it worked, thank you! Do you know of a fast way to join those in Arc though? So far I've been going through and manually inputting "Township+Concession+Lot".
    – Paul
    Nov 13, 2017 at 19:23
  • Nice! By manually you med typing it in for each field? If so dont do that, use Field Calculator. You could also use Python. Could you add a screenshot of the attribute table and the csv file?
    – BERA
    Nov 13, 2017 at 19:28
  • @BERA I added the images above. The .csv became a mess though, for some reason. I have yet to learn python though, and the shapefile does not appear in the field calculator, I assume this is because it's simply a polygon file (broken down into lots).
    – Paul
    Nov 13, 2017 at 19:40

1 Answer 1

Reset to default
2

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: enter image description here

Improve this answer
1
  • Thank you so much for taking the time to do all of this! It has really helped me.
    – Paul
    Nov 13, 2017 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.