How to prevent ADO.NET from altering double values when it reads from Excel files

Question

I have the following rows in my Excel input file:

Column1       Column2
0-5           3.040 
6             2.957 
7             2.876

and the following code which uses ADO.NET to read it:

string fileName = "input.xls";
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
try
{
    var dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
    var dbReader = dbCommand.ExecuteReader ();

    while (dbReader.Read())
    {
        string col1 = dbReader.GetValue(0).ToString();
        string col2 = dbReader.GetValue(1).ToString();                 
    }                
}
finally
{
    dbConnection.Close();
}

The results are very disturbing. Here's why:

The values of each column in the first time through the loop:

col1 is empty (blank)

col2 is 3.04016411633586

Second time:

col1 is 6

col2 is 2.95722928448829

Third time:

col1 is 7

col2 is 2.8763272933077

The first problem happens with col1 in the first iteration. I expect 0-5. The second problem happens at every iteration with col2 where ADO.NET obviously alters the values as it reads them. How to stop this mal-behavior?

Answer 1

By default, ADO.NET infers the column type based on the majority type in the first 8 rows, assumes the majority type wins if there are multiple types (and numeric in case of a tie) and returns NULL for any row that does not match its inferred data type for that column. See here for some notes on this and how to fix it. In a nutshell:

• Add an "IMEX=1" attribute to your connection string.

• In the registry, set the following keys:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes = "Text" Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows = 0

This tells ADO to assume a text data type if it can't decide, and to scan the first 16384 rows (in previous versions of Excel, this was all rows...) to infer the type.

Answer 2

The double type is an approximation of most numbers, and you're seeing more decimals of the approximation in your col2. Simply read it in as a double and convert it to a string representation with 3 digits after the decimal and they'll match.

string col1 = dbReader.GetString(0);
var col2Value = dbReader.GetDouble(1);
string col2 = col2Value.ToString("F3");

Answer 3

Try doing:

string col1 = dbReader.GetString(0); 
string col2 = dbReader.GetDecimal(1).ToString();  

Optionally you can format the ToString to fit display how you would like:

string col2 = dbReader.GetDecimal(1).ToString("0.000");