Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to read dates using xlrd?

This is the code where "rec" variable is used to read the dates in excel sheet but its printing float value how to print that in date format for example '2015:09:02'

for rec in sorted(out.keys()):
print rec #printing float values
print str(out[rec])

I got output:

42240.0
24
like image 625
doubts Avatar asked Sep 07 '15 03:09

doubts


Video Answer


1 Answers

Excel internally stored date values as floats. So in xlrd if you want to read Excel date values as Python date values, you have to use the xldate_as_tuple method to get the date.

Documentation: http://www.lexicon.net/sjmachin/xlrd.html#xlrd.xldate_as_tuple-function

Here's a generic Example:

import datetime, xlrd
book = xlrd.open_workbook("myexcelfile.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

If you create the file myexcelfile.xls and enter a date in cell A1 and run the above code, you should be able to see the correct datetime value in the a1_as_datetime variable.

like image 122
Shiva Avatar answered Oct 20 '22 06:10

Shiva