Reading data from Excel file: one column is retrieving System.DBNull value instead of real value-CodePudding

I'm building a Windows Forms app which reads an Excel 2013 file (*.xlsx). This file contains data structured like a table. The method I use to read data from this file is described here:

How to read data from excel file using c# [duplicate]

This method uses an OleDbConnection object along with a connection string like connecting to a database. Also uses an OleDbCommand object to supply a query to retrieve data. And also uses an OleDbDataReader object to read the retrieved data.

The problem is that when I read some particular rows of this Excel file, the OleDbDataReader object is retrieving in some columns the correct value, but in one column in particular is retrieving a System.DBNull value when there is really data in that column!!!

Example:
- My Excel file:

ClientId    ClientName    Total     InvoiceNumber
---------------------------------------------------
...
4           CLIENT_4      400.00    EV1234
5           CLIENT_5      500.00    EV56.78
6           CLIENT_6      600.00    EV9012
...

Debbugging my app, line by line, adding an expression in "Inspection 1" window, when I reach row 4 for "CLIENT_4": the values are retrieving correctly:

For column ClientId ==> Data is 4.0 and type is object{double}
For column ClientName ==> Data is CLIENT_4 and type is object{String}
For column Total ==> Data is 400.00 and type is object{decimal}
For column InvoiceNumber ==> Data is EV1234 and type is object{double}

But when I reach row 5 for "CLIENT_5": the InvoiceNumber column value is retrieving incorrectly:

For column ClientId ==> Data is 5.0 and type is object{double}
For column ClientName ==> Data is CLIENT_5 and type is object{String}
For column Total ==> Data is 500.00 and type is object{decimal}
For column InvoiceNumber ==> Data is {} and type is object{System.DBNull}
when column value in the Excel file is really "EV56.78"

This is happening for every column with the same pattern (A string with numbers and a decimal period).

1. Why OleDbDataReader object is taking this column value for a NULL when value is like this pattern instead of reading it like a String??? Is decimal period messing with data type when the datareader retrieves it???
2. Is cell formatting from my Excel file messing with data type??? The whole column and all cells in column "InvoiceNumber" have "General" format.
3. Do I have to add an extra parameter to OleDbDataReader object for parsing column values or something similar???

I'm developing on Visual Studio 2010 Ultimate Edition; 64bit O.S.; reading an Excel file from MS-Office 2013.
If you need extra information or code snippets, I'll edit the question to explain myself more (I think this info is fairly enough).

Thanks in advice for any help if you faced something similar to this issue!!!

CodePudding user response：

Add IMEX=1 in you Connection string.

i.e.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcelFile.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Cause:

Excel is inferring the data type of each column from the first few rows. When it then encounters a value that does not match the inferred data type, it treats it as null.

Resource:

From ConnectionStrings

If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.

CodePudding user response：

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Use above one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash. Excel connection strings