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?

No, I just query for value. If the cell contains anything, counter should increment, if it contains nothing kicker should become 1. I run the query on values in the Serial Number column. — Cornel Verster, Aug 17 '15 at 13:05

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

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?.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 17 '15 at 13:08

wenzul

3,948
2
21
33

Thanks! I found an even easier way as I know the position of the first entry and I then subtract it from nrows to find the number of entries. That would, however, not work if there were entries below my table. The empty cell checks are valuable. – Cornel Verster Aug 17 '15 at 13:13
@CornelVerster I don't know what you mean. I adjusted my answer to your given start row 5. So it is working for you now? – wenzul Aug 17 '15 at 13:16
It is, but I can simply do: lastRow = nrows - 5 :) as there are no entries after my table. – Cornel Verster Aug 17 '15 at 13:30
@CornelVerster Sure but I don't get the advantage of `lastRow`. You need the absolute index to query the cell with `xlrd`. – wenzul Aug 17 '15 at 13:33

XLRD Out of Range Error

1 Answers1