Reading date as a string not float from excel using python xlrd [duplicate]

Question

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.

@Linuxios You definitely misunderstood. Excel stores dates as floats. For example, 11-1-88 is stored as 32448.0. — Wilduck
– Wilduck, Commented Dec 19, 2012 at 23:30

j-i-l · Accepted Answer · 2012-12-19 23:37:07Z

78

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

edited Dec 19, 2012 at 23:37

answered Dec 19, 2012 at 23:31

j-i-l

11.1k3 gold badges60 silver badges74 bronze badges

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

5 Comments

Kundan Kumar Over a year ago

@dedoco....is there any way to edit date mode so that in output we have date in mm/dd/yyyy format.

j-i-l Over a year ago

@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()

j-i-l Over a year ago

@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 ;).

Shankar ARUL Over a year ago

stackoverflow.com/a/41155121/786326

Bart Boersma Over a year ago

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

Collectives™ on Stack Overflow

Reading date as a string not float from excel using python xlrd [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related