Take the 2-minute tour ×

Possible Duplicate:
How do I read a date in Excel format in Python?

My date can be among any field in an excel file but when I read it using python xlrd its being read as a float. Is there a way to read all the excel cells as string?

I want to prepare a script to generate a file having all the values in excel file separated by a pipe but this date thing is creating problem.

share|improve this question
can you post your current code and some sample data? –  Harpal Dec 19 '12 at 23:22
@Linuxios You definitely misunderstood. Excel stores dates as floats. For example, 11-1-88 is stored as 32448.0. –  Wilduck Dec 19 '12 at 23:30
@Wilduck: Oh. Sorry. –  Linuxios Dec 19 '12 at 23:32
add comment

marked as duplicate by Wilduck, Peter O., Ram kiran, davidethell, jasonbar Dec 20 '12 at 4:15


1 Answer

up vote 8 down vote accepted

Excel stores dates as floats. If you want to convert them xlrd has a function to help you with this: xldate_as_tuple

An exmple:

import datetime, xlrd
book = xlrd.open_workbook("myfile.xls")
sh = book.sheet_by_index(0)
a1 = sh.cell_value(rowx=0, colx=0)
a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(a1, book.datemode))
print 'datetime: %s' % a1_as_datetime
share|improve this answer
@dedoco....is there any way to edit date mode so that in output we have date in mm/dd/yyyy format. –  Kundan Kumar Dec 19 '12 at 23:47
@KundanKumar datetimehas a variety of output options, to my knowledge (I'm not very familiar with datetime) the closes format to what you want is the ISO 8601 format (YYYY-MM-DD) which you get by: a1_as_datetime.date().isoformat() –  jojo Dec 19 '12 at 23:56
@KundanKumar the point is, excel stores only the float, so if you want to print the date in a readable format, I'm afraid you will have to convert it first. There is no way around this. ...if you are happy with my answer, please consider to mark it as the right answer ;). –  jojo Dec 19 '12 at 23:59
add comment
