ExcelFile and #N/A being read as 42

[ruidc]

I've got an excel file created from Paste Values that when read with pandas.ExcelFile returns int 42 for #N/A values.

Looking at http://stackoverflow.com/questions/4928629/xlrd-excel-script-converting-n-a-to-42 it would seem I'd need to be able to access the xlrd cell type rather than the value to interpret as NaN. Is there a way to do this?

Regards,
RuiDC

[ruidc]

I should add, other than using na_values=[42] in the parse - to avoid skipping any "real" 42 values.

[Chang She]

On Tue, May 8, 2012 at 5:11 AM, ruidc <Ru...@yahoo.com> wrote:

I should add, other than using na_values=[42] in the parse - to avoid skipping any "real" 42 values.

We'll have to add this to the Excel parser in pandas. I made a github issue for pandas about this: https://github.com/pydata/pandas/issues/1213. Any additional input/feedback/sample files would be appreciated.

For a workaround right now, I think you can replace the NA values in the Excel sheet with something like the string 'MISSING' and put that in na_values.

[ruidc]

yep, thx!

[ruidc]

I've made a tweak and filed this pull request which includes sample file and test

https://github.com/pydata/pandas/pull/1225