XLRD Out of Range Error

Question

I have a excel spreadsheet containing data as follows

Serial Number    SAMPLE ID    SAMPLE NAME
value            value        value
value            value        value
value            value        value......

Basically a table of entries. I do not know how many entries the table will have in it. Now I write Python code with xlrd to extract the values from Excel. The first thing that I want to do is determine the amount of entries present, so I use the following piece of code:

kicker = 0
counter = 0
rownum = 5
colnum = 1

while (kicker == 0):
    if sh.cell_value(rowx=rownum, colx=colnum) is None:
        kicker = 1
    else:
        counter = counter + 1
        rownum = rownum + 1

print("done")

The code scans through the values and successfully reads the entries that have a value in the first field. The problem is, when I get to the first row without a value in the first field, xlrd gives me a "list index out of range" error. Thus, I read the last valid value, but as soon as I read the first empty block, it gives the error. How can I determine the amount of entries in my "table" without having xlrd throw an out of range error?

Answer 1

You should query for nrows and not use an potentional endless loop.

kicker = 0
counter = 0
colnum = 1

for rownum in range(5, sh.nrows):
    if sh.cell_type(rowx=rownum, colx=colnum) in (xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK):
        kicker = 1
    else:
        counter = counter + 1

print("done")

Testing an empty cell I looked up here How to detect if a cell is empty when reading Excel files using the xlrd library?.