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.