Python 2.7 - xlrd - Matching A String To a Cell Value

Using Python 2.7 on Mac OSX Lion with xlrd

My problem is relatively simple and straightforward. I'm trying to match a string to an excel cell value, in order to insure that other data, within the row that value will be matched to, is the correct value.

So, say for instance that player = 'Andrea Bargnani' and I want to match a row that looks like this:

 Draft      Player                        Team
 1      Andrea Bargnani -   Toronto Raptors

I do:

 num_rows = draftSheet.nrows - 1
 cur_row = -1
 while cur_row < num_rows:
     cur_row += 1
     row = draftSheet.row(cur_row)
     if row[1] == player:
           ranking == row[0]

The problem is that the value of row[1] is text:u'Andrea Bargnani, as opposed to just Andrea Bargnani.

I know that Excel, after Excel 97, is all unicode. But even if I do player = u'Andrea Bargnani' there is still the preceding text:. So I tried player = 'text:'u'Andrea Bargnani', but when the variable is called it ends up looking like u'text: Andrea Bargnani and still does not produce a match.

I would like to then just strip the test: u' off of the returned row[1] value in order to get an appropriate match.

Solution

You need to get a value from the cell.

I've created a sample excel file with a text "Andrea Bargnani" in the A1 cell. And here the code explaining the difference between printing the cell and it's value:

import xlrd


book = xlrd.open_workbook("input.xls")
sheet = book.sheet_by_index(0)

print sheet.cell(0, 0)  # prints text:u'Andrea Bargnani'
print sheet.cell(0, 0).value  # prints Andrea Bargnani

Hope that helps.