Validating a cell value using XLRD

Question

I am trying to use the package XLRD to read the value of row 2(row 1 in excel), in particular column A.

I know that the value is "text:u'NULL'" but if I try to write an if function which compares a variable which I have given the same value, it does not recognise them as the same.

(Just to explain my spreadsheet has the value Null in the cell I am referring to but using XLRD it reads this as "text:u'NULL'" which is fine)

I have created a test example of what I am referring to as this is hard to describe. The value for b on line 20 of my code is definitely "text:u'NULL'", so I have no idea why it does not read this as equal to b.

import xlrd

book = xlrd.open_workbook("excelscores.xls")
sheet_name = book.sheet_names()[0]
sheet = book.sheet_by_name(sheet_name)
row_no = 1
row = sheet.row(row_no)

## successful test
a = ['lewis','dylan']
b = a[0]
c = 'lewis'
if c == b:
    print "c is equal to b"
else:
    print "fail"

## test that fails
a = row
b = a[0]
c = "text:u'NULL'"
if c == b:
    print "c is equal to b"
else:
    print "fail"enter code here

what is the output when you try print sheet(row_no, 0).value — user3203010, May 01 '15 at 14:32
I have had another person recommend to me that should maybe update to a newer version of Python (currently on 2.7). Not really sure though? Thanks for your help — LemusThelroy, May 02 '15 at 18:10

score 2 · Accepted Answer · edited May 21 '15 at 19:39

The above two values are not equal because they are of different types.

Let's say I have this table:

| Name | Age |   
| John | 22  |  
| Phil | 25  |

Now I want to read the cell (2,1).
>>> cell1 = sheet.cell(1,0)
>>> print cell1
text:u'John'

>>> match_string = "text:u'John'"

Now match_string and cell1 appear same. Let's check if they are equal.

>>> cell1 == match_string
False

They are not equal. Let's check if they are of the same type:

>>> isinstance(cell1, basestring)
False
>>> type(cell1)
<class 'xlrd.sheet.Cell'>

They are of different types so we just can't compare them.

Solution: We can compare using the value attribute of a cell.

>>> cell1_value = cell1.value
>>> print cell1_value
u'John'

This is of the type string which now can be used to compare with string type.

>>> cell1_value==u'John'
True

So in your case, you can do:
>>> row[0].value == u'Null'
True

This method can also be used to compare for numbers or to check for any condition.

Validating a cell value using XLRD

1 Answers1

Linked