Updating existing layer with Excel data

Question

I am trying to update an existing field in a layer with data from an Excel spreadsheet.  It would be based on addresses.  If there were no matches in the spreadsheet, the existing layer would keep the data in the field; if it was in the spreadsheet, it would update to the new data.  Below is what I want to accomplish (with over 7,000 addresses):

Existing LayerAddressInfo1 Main StreetA2 Main StreetB3 Main StreetC

Excel DataAddressInfo1 Main StreetD3 Main StreetF

Updated LayerAddressInfo1 Main StreetD2 Main StreetB3 Main StreetF

I hope I am making sense.  I thought it would be relatively simple to do but I am spinning my wheels.

Thank you.

Priya_Das · Answer

Hi @DaleWalker

Add it to your ArcGIS Pro project by dragging it into the map or using Add Data.

Join the Excel Table to Your Layer: https://support.esri.com/en-us/knowledge-base/how-to-join-an-excel-spreadsheet-to-a-feature-class-in--000021757

Calculate Field to Update Info:https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/calculate-field.htm

JasminePrater · Answer

These are the same steps I would recommend.  I follow these steps to do multiple weekly updates and found it to be the most straightforward and simplistic.

AustinAverill · Answer

Here would be the pythonic way. Note that the `row[i] = ` rows are updating based on index position in the flds list. And for your flds list, you really only need the unique_id column (OBJECTID, GlobalID, etc) and whatever fields you would want to update.

import arcpy
import pandas as pd

flds = ['Unique_ID', 'Address', 'Info']
fc_path = 'my.gdb\featureclass'
excel_path = 'my\excelfile.xlsx'

#excel_data
df = pd.read_excel(excel_path)

with arcpy.da.UpdateCursor(fc_path, flds) as cursor:
    for row in cursor:
        #filters df to matching unique record
        update = df.loc[df['Unique_ID'] == row[0]]

#checks for records
        if len(update) == 0:
            print(f'No updates for {row[0]}')
        elif len(update) > 1:
            print(f'More than one matching record, {row[0]} was mot updated')
        elif len(update) == 1:
            #updates address
            row[1] = df.iloc[-1]['Address']

#updates info
            row[2] = df.iloc[-1]['Info']

cursor.updateRow(row)

If you are intent on using something like address to identify which rows need to be updated, It would be advisable to add additional logic handles strings appropriately for matching records in terms of mismatch case, whitespace, etc. But that would be something you would need to sort out based on your data!