CODEWITHSUNDEEP
pythonexceldatetimexlrd
toy
toy

Reputation: 12161

Python xlrd read DateTime as serial number

I have an excel which is a DateTime format. But when I read the sheet it reads as serial number.

For example, in the sheet it's 08/03/2015 but xlrd reads as 42219.

The code is pretty simple

workbook = xlrd.open_workbook(file_contents=excel_contents)
the_sheet = workbook.sheet_names()[0]
number_of_rows = the_sheet.nrows - 1
curr_row = 0
data = []

while curr_row < number_of_rows:
    curr_row += 1
    air_date = the_sheet.cell_value(curr_row, 1)
    air_time = the_sheet.cell_value(curr_row, 2)
    schedule_time = '{}:{}'.format(air_date, air_time)
    unaware = datetime.strptime(schedule_time, '%m/%d/%Y:%I:%M %p')

The air_date which I use to translate to datetime in Python bombs out because the format is different.

This is the example excel.

enter image description here

And here's the error I got

builtins.ValueError ValueError: time data '42219.0:06:00 AM' does not match format '%m/%d/%Y:%I:%M %p'

Upvotes: 2

Views: 807

Answers (1)

Peut22
Peut22

Reputation: 304

It seems you need to use xldate_as_tuple to convert the excel format into a python format ( See documentation and this already existing question where you will find the format to convert the date to python.

xldate_as_tuple :

Convert an Excel number (presumed to represent a date, a datetime or a time) into a tuple suitable for feeding to datetime or mx.DateTime constructors.

Upvotes: 1

Related Questions