Each Answer to this Q is separated by one/two green lines.
I am not quite sure how to use the following function:
for the following data
Could someone please give me an example on usage of the function for the data?
Quoth the documentation:
Dates in Excel spreadsheets
In reality, there are no such things.
What you have are floating point
numbers and pious hope. There are
several problems with Excel dates:
(1) Dates are not stored as a separate
data type; they are stored as floating
point numbers and you have to rely on
(a) the “number format” applied to
them in Excel and/or (b) knowing which
cells are supposed to have dates in
them. This module helps with (a) by
inspecting the format that has been
applied to each number cell; if it
appears to be a date format, the cell
is classified as a date rather than a
number. Feedback on this feature,
especially from non-English-speaking
locales, would be appreciated.
(2) Excel for Windows stores dates by
default as the number of days (or
fraction thereof) since
1899-12-31T00:00:00. Excel for
Macintosh uses a default start date of
1904-01-01T00:00:00. The date system
can be changed in Excel on a
per-workbook basis (for example: Tools
-> Options -> Calculation, tick the “1904 date system” box). This is of
course a bad idea if there are already
dates in the workbook. There is no
good reason to change it even if there
are no dates in the workbook. Which
date system is in use is recorded in
the workbook. A workbook transported
from Windows to Macintosh (or vice
versa) will work correctly with the
host Excel. When using this module’s
xldate_as_tuple function to convert
numbers from a workbook, you must use
the datemode attribute of the Book
object. If you guess, or make a
judgement depending on where you
believe the workbook was created, you
run the risk of being 1462 days out of
(3) The Excel implementation of the
Windows-default 1900-based date system
works on the incorrect premise that
1900 was a leap year. It interprets
the number 60 as meaning 1900-02-29,
which is not a valid date.
Consequently any number less than 61
is ambiguous. Example: is 59 the
result of 1900-02-28 entered directly,
or is it 1900-03-01 minus 2 days? The
OpenOffice.org Calc program “corrects”
the Microsoft problem; entering
1900-02-27 causes the number 59 to be
stored. Save as an XLS file, then open
the file with Excel — you’ll see
which I quote here because the answer to your question is likely to be wrong unless you take that into account.
So to put this into code would be something like:
import datetime import xlrd book = xlrd.open_workbook("myfile.xls") sheet = book.sheet_by_index(0) cell = sheet.cell(5, 19) # type, <class 'xlrd.sheet.Cell'> if sheet.cell(5, 19).ctype == 3: # 3 means 'xldate' , 1 means 'text' ms_date_number = sheet.cell_value(5, 19) # Correct option 1 ms_date_number = sheet.cell(5, 19).value # Correct option 2 year, month, day, hour, minute, second = xlrd.xldate_as_tuple(ms_date_number, book.datemode) py_date = datetime.datetime(year, month, day, hour, minute, nearest_second)
which gives you a Python datetime in
py_date that you can do useful operations upon using the standard datetime module.
I’ve never used xlrd, and my example is completely made up, but if there is a
myfile.xls and it really has a date number in cell F20, and you aren’t too fussy about precision as noted above, this code should work.
The documentation of the function (minus the list of possible exceptions):
xldate_as_tuple(xldate, datemode) [#]
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. xldate The Excel number datemode 0: 1900-based, 1: 1904-based. WARNING: when using this function to interpret the contents of a workbook, you should pass in the Book.datemode attribute of that workbook. Whether the workbook has ever been anywhere near a Macintosh is irrelevant. Returns: Gregorian (year, month, day, hour, minute, nearest_second).
As the author of xlrd, I’m interested in knowing how the documentation can be made better. Could you please answer these:
Did you read the general section on dates (quoted by @msw)?
Did you read the above specific documentation of the function?
Can you suggest any improvement in the documentation?
Did you actually try running the function, like this:
>>> import xlrd >>> xlrd.xldate_as_tuple(39274.0, 0) (2007, 7, 11, 0, 0, 0) >>> xlrd.xldate_as_tuple(39274.0 - 1.0/60/60/24, 0) (2007, 7, 10, 23, 59, 59) >>>
Use it as such:
number = 39274.0 book_datemode = my_book.datemode year, month, day, hour, minute, second = xldate_as_tuple(number, book_datemode)
import datetime as dt import xlrd log_dir="C:\\Users\\" infile="myfile.xls" book = xlrd.open_workbook(log_dir+infile) sheet1 = book.sheet_by_index(0) date_column_idx = 1 ## iterate through the sheet to locate the date columns for rownum in range(sheet1.nrows): rows = sheet1.row_values(rownum) ## check if the cell is a date; continue otherwise if sheet1.cell(rownum, date_column_idx).ctype != 3 : continue install_dt_tuple = xlrd.xldate_as_tuple((rows[date_column_idx ]), book.datemode) ## the "*date_tuple" will automatically unpack the tuple. Thanks mfitzp :-) date = dt.datetime(*date_tuple)
Here’s what I use to automatically convert dates:
cell = sheet.cell(row, col) value = cell.value if cell.ctype == 3: # xldate value = datetime.datetime(*xlrd.xldate_as_tuple(value, workbook.datemode))