[ACCEPTED]-Python xlrd read as string-xlrd

Accepted answer
Score: 10

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 datemode attribute of the Book object.

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.

Score: 7

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!


sheet 1:listing

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  

sheet 2:colors

status  color
active  red
dormant yellow
extinct green
Score: 4

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