Win32com has problems writing date value using python
I wrote a python script a year ago, it has been working pretty well since then. It writes some values that I retrieve from oracle database into excel spreasheet using search cursor from ArcGIS arcpy module and then write to excel spreasheet using win32com .
I got an error lately and I think I have identified the problem. The error is as follows:
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 freezing while writing a certain value I am pulling from the oracle database when it tries to write excel in line:
sheet.Cells(excelrow,excelcol).Value = rowItem
So, I printed out the value of the rowItem and the type that returns:
- value = 12/05/1193
- type = datetime.datetime
At this point the script has written one hundred lines of date values, so this is very important for this value. I don't think he likes a year on this day. 1193. Does excel have year limits from datetime.datetime value? I don't see it, but it's the only thing that really comes to mind.
I know this value is not correct, as our entries for this particular database should not go beyond ~ 1890 or so. It will take more time and energy to change the value than is appropriate (I work for the government and do not own the database, so I have no permission to change it). Also, I am afraid that I will run into more of these errors, so I would better deal with them and then change them.
Here's a more complete script I quickly wrote to check it 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 tried to convert to string:
str(rowItem)
But this is causing a whole host of new problems (Unicode errors for other values, etc.), so I want to resolve this one instance. Can anyone see what this might be causing this error?
Thank!
source to share
The question 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 the likelihood that the date view in Excel may not come earlier than 1/0/1900
. The way it represents dates is actually the same as a number, so dates before 1990 must be negative. Excel cannot handle this and gives an error message.
If you cannot change the date you are getting from the data source then the question is how you want to handle it. There are several possible options, I suggesttry...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)
Unfortunately, you can't catch the error right away, so you have to use naked except
, but that will throw 800 years on any date it can't write.
source to share