Select to view content in your preferred language

Updating existing layer with Excel data

254
4
10-29-2025 06:34 AM
DaleWalker
New Contributor

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 Layer
AddressInfo
1 Main StreetA
2 Main StreetB
3 Main StreetC

 

Excel Data
AddressInfo
1 Main StreetD
3 Main StreetF

 

Updated Layer
AddressInfo
1 Main StreetD
2 Main StreetB
3 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.

0 Kudos
4 Replies
Robert_LeClair
Esri Esteemed Contributor

A manual approach would be something like this:

1.  Add a Join from your Excel spreadsheet to the feature class using the Address field in both tables as your common field using a 1-1 cardinality.  Now you have one large table where the Excel table is appended to the spatial table.
2.  Run a select layer by attribute where the SQL query is something like Address1<>Address2.  Then you have selected records where Address1 does not equal Address2.
3.  Lastly, run a Calculate Field where Address1=Address2.  The result is a newly updated attribute value in the spatial table where the matches are recalculated from the Excel table.

I'd recommend doing this workflow on copies of the data first to make sure it's the output you desire.  I'm sure you could automate this with a Python script or a model in ModelBuilder too.

JasminePrater
Frequent Contributor

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
Frequent Contributor

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!

Priya_Das
Esri Contributor

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...

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