OleDB Jet - Float issues in reading excel data

Question

When I read a sheet into a DataTable using the OleDbDataReader, floating point numbers loose their precision.

I tried forcing OleDb to read the excel data as string, but although the data is now contained in a DataRow with each Column defined as System.String it looses precision (18.125 -> 18.124962832).

Any idea how to avoid this behaviour?

can you post your code snippet here pls? – balalakshmi Apr 11 '10 at 10:44 — balalakshmi, Apr 11 '10 at 10:44

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

I just tested your data and this method posted here worked.
i.e. the cell value kept it's precision 18.124962832, when put into DataSet.

edited May 23 '17 at 12:12

Community

1
1

answered May 12 '11 at 20:24

Brian Wells

1,572
1
14
12

Yes -- the IMEX=1 argument is the one to make this work. I just had to use it a couple of weeks ago. – David-W-Fenton May 13 '11 at 22:21

score 0 · Answer 2 · answered Jun 14 '10 at 18:46

I'm pretty sure that Jet tries to assign a datatype to each column based on what it sees in the first five rows. If something after those first five rows doesn't fall into that data type it will either convert it or return nothing at all.

Do the first five rows of your spreadsheet have a lower precision than the items that are begin truncated?

Take a look at this post.

score -3 · Answer 3 · answered Apr 12 '10 at 14:23

The output from the code below shows you how to get the underlying number and the formatted text with SpreadsheetGear for .NET:

Here is the output from the code:

x=18.124962832 y=18.124962832 formattedText=18.125

Here is the code:

namespace Program
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new workbook and get a reference to Sheet1!A1.
            var workbook = SpreadsheetGear.Factory.GetWorkbook();
            var sheet1 = workbook.Worksheets[0];
            var a1 = workbook.Worksheets[0].Cells["A1"];
            // Put the number in the cell.
            double x = 18.124962832;
            a1.Value = x;
            a1.NumberFormat = "0.000";
            double y = (double)a1.Value;
            string formattedText = a1.Text;
            System.Console.WriteLine("x={0} y={1} formattedText={2}", x, y, formattedText);
        }
    }
}

You can see live SpreadsheetGear samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

However valuable your product may be, I really don't see this as a helpful answer. I don't have the answer myself (I'm only a casual Excel user), but I'm certain it doesn't require purchasing a third-party product. — David-W-Fenton, Apr 12 '10 at 19:48

OleDB Jet - Float issues in reading excel data

3 Answers3