Reputation: 12161
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.
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
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