Errors using arcpy.CalculateField_management

Question

EDIT: What I forgot to mention when I first had this question is that I was trying to do these calculations on a layer which had joins, BUT where the join was on data that was added from an Excel file. ArcGIS does not seem to like data from Excel files.

I am trying to use the arcpy.CalculateField_management function and running into some trouble.

I have a shapefile called district_plans, and to this shapefile I have joined a table of data (the table of data is named kdp). I want to change a field in district_plans to the data in one of the columns of kdp.

Trying the following

arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!kdp$.plan_4!","PYTHON")

yields the following error message

Runtime error Traceback (most recent call last): File "<string>", line 1, in
<module> File "c:\program files x86)\arcgis\desktop10.2\arcpy\arcpy\management.py",
line 3183, in CalculateField raise e ExecuteError: Failed to execute. Parameters 
are not valid. ERROR 000728: Field district_plans.drawplan does not exist within 
table Failed to execute (CalculateField). 

Trying the following

arcpy.CalculateField_management("district_plans","drawplan", "!kdp$.plan_4!","PYTHON")

yields the following error message

Runtime error Traceback (most recent call last): File "<string>", line 1, in 
<module> File "c:\program files x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", 
line 3183, in CalculateField raise e ExecuteError: ERROR 999999: Error executing 
function. Failed to execute (CalculateField). 

The following variations all yield errors:

arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!plan_4!","PYTHON")
arcpy.CalculateField_management("district_plans","drawplan", "!plan_4!","PYTHON")

I am able to manually perform the field calculation using the field calculator on the drawplan column in the attribute table of district_plans, so I am not sure why the Python version does not work.

• mwil: do you get an error with replacing "PYTHON" argument with "PYTHON_9.3" ?
• derNincompoop: @mwil Yes, same type of errors even if I replace "PYTHON" with "PYTHON_9.3".
• Farid Cheraghi: are you using this in python window? or as an standalone script? And try this: arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!kdp$.plan_4!","PYTHON_9.3","#")
• Farid Cheraghi: does your join table name contains $ sign? In your question you have said, "the table of data is named kdp"
• Farid Cheraghi: Try to first export your excel sheet to dbf or geodatabase table and then create your map join, and then calculate. I'm pretty sure the problem is from the excel data source!

Answer 1

The issue was that the data joined to the district_plans layer came from an Excel file as pointed out by @Farid Cher and @Michael Miles-Stimson. Although I was not able to get the Table to Table function to work, I found the following code (located in this GitHub repo) which takes a Pandas dataframe and writes it out as a .dbf.

import pysal as ps
import numpy as np
def df2dbf(df, dbf_path, my_specs=None):
 '''
 Convert a pandas.DataFrame into a dbf.
 __author__ = "Dani Arribas-Bel <[email protected]> "
 ...
 Arguments
 ---------
 df : DataFrame
 Pandas dataframe object to be entirely written out to a dbf
 dbf_path : str
 Path to the output dbf. It is also returned by the function
 my_specs : list
 List with the field_specs to use for each column.
 Defaults to None and applies the following scheme:
 * int: ('N', 14, 0)
 * float: ('N', 14, 14)
 * str: ('C', 14, 0)
 '''
 if my_specs:
 specs = my_specs
 else:
 type2spec = {int: ('N', 20, 0),
 np.int64: ('N', 20, 0),
 float: ('N', 36, 15),
 np.float64: ('N', 36, 15),
 str: ('C', 14, 0)
 }
 types = [type(df[i].iloc[0]) for i in df.columns]
 specs = [type2spec[t] for t in types]
 db = ps.open(dbf_path, 'w')
 db.header = list(df.columns)
 db.field_spec = specs
 for i, row in df.T.iteritems():
 db.write(row)
 db.close()
 return dbf_path 

After joining the data contained in the .dbf file everything works fine.

Answer 2

Alternatively to the Table to Table tool, you could try using the Excel to Table tool. From my experience the Table to Table tool does not always convert the Microsoft Excel sheets properly, while the Excel to Table tool always did the job fine.

Edit: I just realized that this tool is only available for ArcGIS Desktop 10.2 and onwards.