xlrd Excel script converting "#N/A" to 42

Question

I have a script that pulls data out of an excel spreadsheet using the xlrd module, specifically the row_values() method. It appears to do a great job, except for where "#N/A" has been auto-generated by previous VLookups, in which case xlrd gets "#N/A" as integer 42.

I had a look at string formatting methods but couldn't see how that was the issue.

Apart from having a script that has discovered the meaning of life (42), can anyone suggest what the issue may be?

Cheers

Note: The sheet no longer has the Vlookups in it, all values have been copied from other sheets, everything is plain values, no formulas.

Answer 1

I found this useful. Thanks to John's initial help.

def xls_proc_text(cell, value_proc=None, text_proc=None):
    """Converts the given cell to appropriate text."""
    """The proc will come in only when the given is value or text."""
    ttype = cell.ctype
    if ttype == xlrd.XL_CELL_EMPTY or ttype == xlrd.XL_CELL_TEXT or ttype == xlrd.XL_CELL_BLANK:
        if text_proc is None:
            return cell.value
        else:
            return text_proc(cell.value)
    if ttype == xlrd.XL_CELL_NUMBER or ttype == xlrd.XL_CELL_DATE or ttype == xlrd.XL_CELL_BOOLEAN:
        if value_proc is None:
            return str(cell.value)
        else:
            return str(value_proc(cell.value))
    if cell.ctype == xlrd.XL_CELL_ERROR:
        # Apply no proc on this.
        return xlrd.error_text_from_code[cell.value]

Answer 2

xlrd docs on the web (or on your computer; open the docs in your browser and do Ctrl-F #N/A) give you the conversion table from Excel internal codes to text.

It might be of use to look at the sheet.row_types() method, and the Cell class docs which give you the cross-reference between type numbers as returned by sheet.row_types() and others. Note that it is generally more efficient to test for those type numbers than it is to use isinstance() on the values, AND there is no ambiguity using type numbers.

Answer 3

As Andrew listed if you have error in the cell, xlrd writes the code of the error, which you can see here:

0x00: '#NULL!',  # Intersection of two cell ranges is empty
0x07: '#DIV/0!', # Division by zero
0x0F: '#VALUE!', # Wrong type of operand
0x17: '#REF!',   # Illegal or deleted cell reference
0x1D: '#NAME?',  # Wrong function or range name
0x24: '#NUM!',   # Value range overflow
0x2A: '#N/A',    # Argument or function not available

Converting code 0x2A from hex to dec you can get that 42 value. To avoid that you can use something like this in your code:

for rownum in xrange(sh.nrows):
    wr.writerow(['#N/A' if col.ctype == xlrd.XL_CELL_ERROR else col.value for col in sh.row(rownum)])

Answer 4

• I have simplified the solution, Thanks to everyone above. You can Identify Error cell based on cell type.
• The data we have is ctype of #N/A
• value = 42 (instead of #N/A)
• ctype will be 5

Simple solution might be Identify Error cell and put 'None' instead of 42

textType = sheet.cell(r,0).ctype #Get the type of the cell

        if textType == 5:
            text = None
        else:
            text = sheet.cell(r, 0).value

XLRD Documentation:

You can identify the all the other type based on below documentation

XL_CELL_ERROR 5 int representing internal Excel codes; for a text representation, refer to the supplied dictionary error_text_from_code