0

I want to calculate a field in shapefile from a field in Excel sheet using an ArcPy script. How can I write the last line in the below script?

import arcpy
import xlrd
arcpy.env.overwriteOutput = True

inputExcel = arcpy.GetParameterAsText(0)
inputSHP = arcpy.GetParameterAsText(1)

workbook = xlrd.open_workbook(inputExcel)
worksheet = workbook.sheet_by_name('Sheet1')

cursor1 = arcpy.UpdateCursor(inputSHP)

for row1 in cursor1:
    arcpy.CalculateField_management(in_table, field, expression, {expression_type}, {code_block})
4
  • 1
    CalculateField operates over all rows and arcpy.UpdateCursor is deprecated. Start over again using the documentation for arcpy.da.UpdateCursor. If you're updating from the Excel sheet, you should first to extract the values by key field into a dictionary. If you just want to insert values you should use a DA InsertCursor.
    – Vince
    Commented Jun 20, 2021 at 16:04
  • Its worth noting that arcpy can open Excel worksheets as tables directly. No xlrd required. Although, I think it's read only. But can export tables to Excel as well (write-only). Commented Jun 21, 2021 at 0:44
  • Can you add an example of the data in excel and the calculation you are trying to perform?
    – Bera
    Commented Jun 21, 2021 at 5:30
  • It is one field in the excel with yes and no values and another one with dates Commented Jun 21, 2021 at 6:58

1 Answer 1

1

You can do this without coding (or with just 1 line of code) with the Join Fields tool.

Else Vince's suggestion of turning the excel join & value field into a dictionary is good.

Yet another way is to convert the Excel table to a temporary table, then just use the regular AddJoin & CalculateField workflow.

import arcpy

inputExcel = arcpy.GetParameterAsText(0)
inputSHP = arcpy.GetParameterAsText(1)

arcpy.conversion.ExcelToTable(inputExcel, "in_memory\\temp_tab1", 'Sheet1')

# make a layer and add the join 
arcpy.management.MakeFeatureLayer(inputSHP, "temp_lyr")
arcpy.management.AddJoin("temp_lyr", "join_field1", "in_memory\\temp_tab1", "join_field2")

# transfer the values
arcpy.management.CalculateField("temp_lyr", "target_field", "!source_field!")

Your Answer

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

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