[ACCEPTED]-Python xlrd read as string-xlrd
xlrd does NOT convert dates to float. Excel 21 stores dates as floats.
Quoting from the xlrd documentation (scroll 20 down a page):
Dates in Excel spreadsheets
In reality, there are no such 19 things. What you have are floating point numbers 18 and pious hope. There are several problems 17 with Excel dates:
(1) Dates are not stored 16 as a separate data type; they are stored 15 as floating point numbers and you have 14 to rely on (a) the "number format" applied 13 to them in Excel and/or (b) knowing which cells 12 are supposed to have dates in them. This 11 module helps with (a) by inspecting the 10 format that has been applied to each number 9 cell; if it appears to be a date format, the 8 cell is classified as a date rather than 7 a number.
(2) ... When using this package’s 6
xldate_as_tuple()function to convert numbers from a workbook, you 5 must use the
datemodeattribute of the
See 4 also the section on the Cell class to learn about 3 the type of cells, and the various Sheet methods which 2 extract the type of a cell (text, number, date, boolean, etc).
Check 1 out python-excel.org for info on other Python Excel packages.
well, as you say:
# reading from a xls file (no .xlsx files, no writing!) import xlrd # install xlrd from http://pypi.python.org/pypi/xlrd wb = xlrd.open_workbook("YOUR_FILE.xls") # xls file to read from sh1 = wb.sheet_by_index(0) # first sheet in workbook sh2 = wb.sheet_by_name('colors') # sheet called colors # print all rows in first sheet print "content of", sh1.name # name of sheet for rownum in range(sh1.nrows): # sh1.nrows -> number of rows (ncols -> num columns) print sh1.row_values(rownum) # rowx and colx (x for Excel) start at 1! print "row3 col 2:", sh1.cell(rowx=3,colx=2).value col = sh1.col_values(0) # column 0 as a list of string or numbers print '"A" column content:' # python index 0, 1.colunm, called A for cell in col: print cell print sh1.col_values(1) # 2. column, note mix of string (header) and numbers!
FOR THIS EXAMPLE THE XLS 1 is:
name latitude longitude status color date Mount Hood 45.3736 121.6925 active red 01-ene-01 Mount Jefferson 44.6744 121.7978 dormant yellow 23-sep-05 Three-Fingered 44.478 121.8442 extinct green Mount Washington 4.3325 121.8372 extinct green South Sister 44.1036 121.7681 active red Diamond Peak 43.5206 122.1486 extinct green Mount Thielsen 43.1531 122.0658 extinct green Mount Scott 42.923 122.0163 dormant yellow Mount McLoughlin 2.445 122.3142 dormant yellow
status color active red dormant yellow extinct green
Excel stores dates as numbers both internally 8 and in .xls files and then formats them 7 accordingly when displaying. Thus, if you 6 read them naively with xlrd, you will get either 5 numbers or strings. What you should do is 4 check what the type of a cell is and then 3 convert the number yourself. Either using 2 xlrd's built-in functions, such as
xldate_as_tuple(), or your 1 own function.
Refer to this question for some more details.
More Related questions