win32com having problems writing date value using python

Question

I wrote a python script a year or so ago that has been working quite well since then. It writes some values I extract from an oracle database to an excel spreasheet using a search cursor from ArcGIS arcpy module and then I write to the excel spreasheet using win32com.

Recently, I have experienced an error that I sort of think I have identified the issue. The error is the following:

sheet.Cells(excelrow,excelcol).Value = rowItem
  File "E:\sw_nt\Python27\ArcGIS10.1\lib\site-packages\win32com\client\__init__.py", line 474, in __setattr__
    self._oleobj_.Invoke(*(args + (value,) + defArgs))
com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

So, its hanging on writing a particular value I pull from the oracle database when it tries to write to excel in the line:

sheet.Cells(excelrow,excelcol).Value = rowItem

So, I printed out the value of rowItem and the type, which returns:

• value = 12/05/1193
• type = datetime.datetime

The script has written a hundred or so rows of date values at this point, so it seems to be very particular to this one value. I don't think excel likes the year in that date. 1193. Does excel have limitations on the year from a datetime.datetime value? I can't see it, but its the only things that really comes to mind.

I know this value is to be wrong as our records for that particular database should not go beyond ~1890 or so. To change the value would take more time and energy than is worthwhile (I work for government and do not own the database, therefore I don't have rights to change it) Also, I am afraid that I will come across more of these errors, so I would rather handle them, then change them.

Here's a more complete script I quickly wrote to test this out:

import arcpy, win32com.client

# Set a variable to an empty excel instance
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True

# Initialize a workbook within excel
book = excel.Workbooks.Add()

# Set first sheet in book and rename it for the report
sheet = book.Worksheets(1)
sheet.Columns(1).ColumnWidth = 30

x = r"path_to_Database"

excelrow = 1

for row in arcpy.SearchCursor(x):
    sheet.Cells(excelrow,1).Value = row.WORK_START_DATE
    excelrow +=1

I have tried to convert to string:

str(rowItem)

But that brings up a whole host of new problems (unicode errors on other values, etc) so I want to resolve this one instance. Can anyone see what it is that may be causing this error?

Thanks!

Answer 1

The issue is, as you correctly suspect, the year. I confirmed this with the following code:

>>> d = datetime.datetime(1193, 12, 5)
>>> sheet.Cells(1,1).Value = d
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python27\lib\site-packages\win32com\client\__init__.py", line 474, in __setattr__
    self._oleobj_.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)
>>> d = datetime.datetime(1993, 12, 5)
>>> sheet.Cells(1,1).Value = d
>>>

The reason for this is likely because Excel's date representation can go no earlier than 1/0/1900. The way it represents dates is actually with a number, so dates earlier than 1990, would need to be negative. Excel can't handle this, and gives you an error message.

If you cannot change the date you get from your data source, then the question becomes how do you want to handle it. There are several potential options, I suggest try...except

try:
    sheet.Cells(excelrow, 1).Value = row.WORK_START_DATE
except:  
    datetime.datetime(row.WORK_START_DATE.year + 800, row.WORK_START_DATE.month, row.WORK_START_DATE.day)

Unfortnately, you can't directly catch the error, so you need to use a bare except, but this will throw 800 years onto any date that fails to write.