Python reading date from excel throws error

Question

I am trying to read date from excel file using xlrd module. Below is my code for this :

# Variables
myfile = '/home/mobaxterm/.git/Operation_Documentation/docs/Servicing Portal User &  Certificate Inventory.xlsx'
mydate = 'Expiration Date'
row_head = 0

# Import required modules
import xlrd
import datetime


today = datetime.date.today()
book = xlrd.open_workbook(myfile)
sheet = book.sheet_by_index(1)
for col_index in range(sheet.ncols):
        print xlrd.cellname(row_head,col_index),"-",
        print sheet.cell(row_head,col_index).value
        if sheet.cell(row_head,col_index).value == mydate:
                for raw_index in range(sheet.nrows):
                        expire = sheet.cell(raw_index,col_index).value
                        print expire
                        expire_date = datetime.datetime(*xlrd.xldate_as_tuple(expire, book.datemode))    
                        print 'datetime: %s' %  expire_date
                break

While running the code i am getting following error :

Traceback (most recent call last):
  File "cert_monitor.py", line 31, in <module>
    expire_date = datetime.datetime(*xlrd.xldate_as_tuple(expire, book.datemode))
  File "/usr/lib/python2.6/site-packages/xlrd/xldate.py", line 61, in xldate_as_tuple
    xldays = int(xldate)
ValueError: invalid literal for int() with base 10: 'Expiration Date'

Can anyone suggest what could be the issue here?

Thanks for your time.

Output types of arguments for xlrd.xldate_as_tuple: print type( expire ), print type( book.datemode ); then view a documentation for the function: probably one of the parameters has a wrong type. — sergzach
– sergzach, Commented Jun 19, 2013 at 15:32
Are you sure you are showing the code from line 31? mydate is the only name with the value 'Expiration Date' and the line that uses it does not look like it is the problem. You may need to show a simplified example of the file that shows the problem. — dansalmo
– dansalmo, Commented Jun 19, 2013 at 15:34
@dansalmo, this string is probably the header in the xls file. — Elazar
– Elazar, Commented Jun 19, 2013 at 15:36
Problematic part in the code is expire_date = datetime.datetime(*xlrd.xldate_as_tuple(expire, book.datemode)) print 'datetime: %s' % expire_date if i remove that part code is working till that point. So when i try to convert excel column data (float) to date format at that time it is throwing the error. — user2501825
– user2501825, Commented Jun 19, 2013 at 15:37

Elazar · Accepted Answer · 2013-06-19 15:38:20Z

1

I believe that you should only skip the header:

for raw_index in range(1, sheet.nrows):
    ...

You are checking that sheet.cell(row_head,col_index).value == mydate, and then you want to iterate over the rows, but you should skip row_head first - it is ==mydate, which is not a date but a simple 'Expiration Date' string.

edited Jun 19, 2013 at 15:38

answered Jun 19, 2013 at 15:32

Elazar

22k4 gold badges51 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2501825 Over a year ago

Its date conversion of excel data which is causing problem. Other code is working absolutely fine.

Elazar Over a year ago

@user2501825 have you tried my solution? your excel data you are trying to convert to date is not really a date. it is the column header.

user2501825 Over a year ago

Yeah after making that change i was able to print first date but still throws the same error and quits.

python cert_monitor.py A1 - Server Name B1 - Task C1 - Username D1 - Purpose E1 - Password Expiration F1 - Expiration Date 41450.0 datetime: 2013-06-25 00:00:00  Traceback (most recent call last):   File "cert_monitor.py", line 31, in <module>     expire_date = datetime.datetime(*xlrd.xldate_as_tuple(expire, book.datemode))   File "/usr/lib/python2.6/site-packages/xlrd/xldate.py", line 61, in xldate_as_tuple     xldays = int(xldate) ValueError: invalid literal for int() with base 10: ''

Elazar Over a year ago

I think you have empty rows there.

user2501825 Over a year ago

You are awesome. After first raw two raws are empty. I guess i have to put logic to ignore empty raws. Thanks for pointing that out. Appreciate your time.

Collectives™ on Stack Overflow

Python reading date from excel throws error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related