Using xlsx with ArcPy?

Question

I am writing a Python script that finds the difference between populations for U.S. counties from one year to another and writes that information to a new field in a xlxs file.

Can it be done with ArcPy and what would the code look like?

Below is a sample of the xlxs data with the column names:

Area_name,     Resident population 1990, Resident population 2000, Resident population 2010
UNITED STATES,                248709873,                281421906,                308745538 
ALABAMA,                        4040587,                  4447100,                  4779736 
Autauga, AL,                      34222,                    43671,                    54571 
Baldwin, AL,                      98280,                   140415,                   182265 
Barbour, AL,                      25417,                    29038,                    27457 
Bibb, AL,                         16576,                    20826,                    22915

I have the below code so far but I'm getting errors:

import arcpy
from arcpy import env
import xlrd 
env.workspace=r'C:\Users\Kevin\Desktop\Geoprograming\week_four' 
workbook = xlrd.open_workbook('test.xlsx')
worksheet = workbook.sheet_names('sheet1')
cursor = arcpy.da.UpdateCursor(population, ["1990", "2000"])
cursor.insertRow([diff90_10])
for row in cursor:
    row[diff90_10] = row[2010]-row[1990]
    cursor.updateRow([row])

Runtime error Traceback (most recent call last): File "", line 9, in File "C:\Python27\ArcGIS10.4\lib\site-packages\xlrd__init__.py", line 394, in open_workbook f = open(filename, "rb") IOError: [Errno 2] No such file or directory: 'test.xlsx'

Answer 1

No, you cannot modify an Excel file from ArcGIS (including Arcpy).

From Understanding how to use Microsoft Excel files in ArcGIS:

Once added to ArcMap, you can open the table from the Source view of the table of contents. However, you will not be able to edit the table or export records to an Excel format.

also:

Excel tables are read-only in ArcGIS as well as in Excel when you have a workbook open in ArcGIS.

You will get error messages if trying to access an Excel file using an arcpy Update Cursor, but you can use Search Cursors to read data from an Excel file.

Error I get using Update Cursor on an Excel file:

RuntimeError was unhandled by user code Message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

If you need to keep it as Excel Files you will need to use Python Excel modules such as xlwt and xlutils. Looking at your code, there probably isn't any need for Arcpy here, you should be able to do the updates you require using Python without arcpy.

See Edit existing excel workbooks and sheets with xlrd and xlwt on Stack Overflow for using xlutils in python to modify an Excel File.

If it doesn't need to be Excel, I would export the Excel file into a new geodatabase table, that way arcpy and Update Cursor would work.

---

A few extra points to note about your code snippet

cursor.insertRow([diff90_10])

This will not add your extra column - An Arcpy cursor's Insert Row adds a new row (record), rather than a new field/column. If this was a geodatabase table or feature class, you could use something like arcpy.AddField_management(mytable, 'diff90_10', 'LONG') to add your new field.

cursor = arcpy.da.UpdateCursor(population, ["1990", "2000"])

population here should be referring to your Table, not to a field. Your fields are ['Resident population 1990', 'Resident population 2000', 'Resident population 2010'] as per your table example, although even that would probably fail as ArcGIS doesn't like spaces in field names.

If updating a geodatabase table, your cursor would need to look something like this:

mytable = r'test.gdb\mytable'
arcpy.AddField_management(mytable, 'diff90_10', 'LONG')
with arcpy.da.UpdateCursor(mytable, ['Resident_population_1990', 'Resident_population_2010', 'diff90_10']) as cursor:
    for row in cursor:
        row[2] = row[1]-row[0]
        cursor.updateRow(row)