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.

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.