How do I read Excel file in Python?

[John Machin]

houdinihound at yahoo.com wrote:
> > > >>> excel_date = 38938.0
> > > >>> python_date = datetime.date(1900, 1, 1) + datetime.timedelta(days=excel_date)
> > > >>> python_date
> > > datetime.date(2006, 8, 11)
> >
> > Err, that's the wrong answer, isn't it? Perhaps it shoud be
> > datetime.date(1900, 1, 29)?
>
> Actually was about to post same solution and got same results. (BTW
> Simon, the OP date is Aug 9th, 2006).  Scratched head and googled for
> excel date calculations... found this bug where it treats 1900 as leap
> year incorrectly:
> http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
>
> Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
> calc:
> >>>python_date = datetime.date(1900, 1, 1) + datetime.timedelta(days=excel_date - 2)
> >>> python_date
> datetime.date(2006, 8, 9)
>

... and 2006-08-09 is only the correct answer if the spreadsheet was,
when last saved, using the 1900 ("Windows") date system, not the 1904
("Macintosh") date system.

All the OP needs to do is to read the documentation that comes with the
xlrd package. It describes the problems with Excel dates, and offers
functions for conversion between the Excel date numbers and (year,
month, day, hour, minute, second) tuples which of course are
interoperable with Python's datetime module and with mx.DateTime.

| >>> import xlrd
| >>> xlrd.xldate_as_tuple(38938.0, 0)
| (2006, 8, 9, 0, 0, 0)
| >>> xlrd.xldate_as_tuple(38938.0, 1)
| (2010, 8, 10, 0, 0, 0)
| >>>

Cheers,
John