Reading a date from an Excel file converts it to a number like 32509.0

Question

When I read a date from an Excel file using the xlrd package, the imported data are not dates but numbers like 32509.0.

The Excel input data are dates like 1989-01-01.

Code:

import xlrd
import mysql.connector
import datetime

name= "Anand1989.xls"
book=xlrd.open_workbook(name)
worksheet = book.sheet_by_index(0)
database = mysql.connector.connect (host="localhost", user = "root", passwd = "", db = "rainfall")
cursor = database.cursor()
query = """INSERT INTO  Anand1989(DATE) VALUES (%s)"""
for r in range(1, worksheet.nrows):
    DATE = worksheet.cell(r,0).value
    date_time_obj = datetime.datetime.strptime(str(DATE), '%Y-%m-%d').date
    values = (date_time_obj)
cursor.execute(query, values)
cursor.close()
database.commit()
database.close() 
print ("All Done! Bye, for now.")

Traceback (most recent call last):
File "newins.py", line 46, in <module> date_time_obj = datetime.datetime.strptime(str(DATE), '%Y-%m-%d').date
File "C:\Users\student\AppData\Local\Programs\Python\Python37-32\lib\_strptime.py", line 577, in _strptime_datetimet tt, fraction,gmtoff_fraction = _strptime(data_string, format)
File "C:\Users\student\AppData\Local\Programs\Python\Python37-32\lib\_strptime.py", line 359, in _strptime (data_string, format)) ValueError: time data '32509.0' does not match format '%Y-%m-%d'

Answer 1

In Excel the dates are numbers, being 1/1/1900 equivalent to 1. So the number 32509 represent the date corresponding to 32508 days after 1/1/1900 or 1/1/1989 (the decimal part corresponds to the time).

You could use xlrd.xldate.xldate_as_tuple(ExcelDate, datemode)

where:

ExcelDate: decimal number representing a datetime.

datemode is 0: for 1900-based or 1: for 1904-based (in this case should be 0)

and returns a tuple of the format (year, month, day, hour, minute, nearest_second)