25

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.

j-i-l
  • 10,281
  • 3
  • 53
  • 70
Kundan Kumar
  • 1,974
  • 7
  • 32
  • 54

1 Answers1

76

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
j-i-l
  • 10,281
  • 3
  • 53
  • 70
  • @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 `datetime`has 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()` – j-i-l 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 ;). – j-i-l Dec 19 '12 at 23:59
  • http://stackoverflow.com/a/41155121/786326 – Shankar ARUL Dec 15 '16 at 01:52
  • Late to the party, but for anyone dropping by: add .strftime("%m/%d/%Y") behind the datetime() method for the mm/dd/yyyy format @KundanKumar is asking for – Bart Boersma Feb 16 '21 at 09:22