CODEWITHSUNDEEP
c#exceloledbnumber-formattingoledbconnection
Arpit Gupta
Arpit Gupta

Reputation: 1277

Reading Excel file with OleDB return wrong values

I am trying to read an Excel File which contains the following kind of value in a column.

enter image description here

Its a datetime while I am looking these column in the formula bar. Some what like below -

enter image description here

I am trying to read these column using the following code.

            var sqlBuilder = new OleDbConnectionStringBuilder
            {
                DataSource = FileName,
                Provider = "Microsoft.ACE.OLEDB.12.0"
            };
            sqlBuilder.Add("Extended Properties", "Excel 12.0;characterset=65001;HDR=No;IMEX=1;FMT=Delimited;");
            cn = new OleDbConnection(sqlBuilder.ConnectionString);
            cn.Open();

But, The problem is that these values are not read exactly the same. They are read like-

enter image description here

Some random numeric value takes places instead of 00. Tried to use Imex=0 or HDR=Yes, but wasn't helpful.

Upvotes: 0

Views: 869

Answers (1)

Bron Davies
Bron Davies

Reputation: 6004

That's because the OleDB ODBC driver is notoriously terrible at guessing column data formats. It's better to tell it exactly how to read the column data type. I prefer to read all the column values in as strings and convert them in my code.

Use this in your connection string builder:

sqlBuilder.Add("Extended Properties", "Excel 12.0 Xml;characterset=65001;HDR=YES;IMEX=1;");

See https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/

You might also be getting the absolute value and not the formatted values you see in Excel in which case your code is working perfectly and you'll have to figure out how to deal with the discrepancies.

If all else fails with using the OleDB method, switch to using EPPlus which I have had much better success with reading XLSX files accurately.

Upvotes: 1

Related Questions