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 | |
| Address | Info |
| 1 Main Street | A |
| 2 Main Street | B |
| 3 Main Street | C |
| Excel Data | |
| Address | Info |
| 1 Main Street | D |
| 3 Main Street | F |
| Updated Layer | |
| Address | Info |
| 1 Main Street | D |
| 2 Main Street | B |
| 3 Main Street | F |
I hope I am making sense. I thought it would be relatively simple to do but I am spinning my wheels.
Thank you.
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.
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.
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!
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