ExcelDataReader reading values like #N/A as string instead of NULL or missing while reading excel file

Question

I'm using ExcelDataReader for reading .xlsx, .xls, .csv file while reading it treats everything as strings so a #N/A or #value! is also treated as a string and gives an exception when I try to do computation on them.

 FileStream stream = File.Open(strFilePath, FileMode.Open, FileAccess.Read);

            //Reading from a binary Excel file ('97-2003 format; *.xls)
            string extension = System.IO.Path.GetExtension(strFilePath).ToLower();
            IExcelDataReader excelReader;
            if (extension.Equals(".csv"))
            {
                excelReader = ExcelReaderFactory.CreateCsvReader(stream);
            }
            else if (extension.Equals(".xls"))
            {
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else
            {
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }

            //...
            //DataSet - The result of each spreadsheet will be created in the result.Tables
            //DataSet - Create column names from first row
            DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });
            excelReader.Close();
            return result.Tables[sheetno];

This is the code I use for importing excel file. How can it be modified to read #N/A or #value! as 0 or NULL?

Answer 1

This is a bug in ExcelDataReader's XLSX parser and needs a code fix in the library.

Bug is tracked here: https://github.com/ExcelDataReader/ExcelDataReader/issues/329

ExcelDataReader with XLS works as expected: it returns NULL for fields with an error.

ExcelDataReader with CSV will likely continue to return the error as a string, because the CSV format does not disambiguate error strings from regular strings.