CODEWITHSUNDEEP
c#winformsexceloledb
Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Oledb reader does not read excel files correctly

I am trying to read a xls file through OLEDB reader, with mixed datatype in one column of xls. Oledb returns me DBNULL for these column Values. i have 05-06-12 in first cell of this column. when i goes for checking format cell, it shows me custom type with dd-mm-yy and in all other cells it shows General format. I am using following connection string

mCon.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + mstrFilePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";");

I have following data in xls

OLEDB Reads it like this

What could be the reason ? Even i put IMEX = 1 in connection string !!!

Upvotes: 4

Views: 6713

Answers (4)

1fuyouinfinite
1fuyouinfinite

Reputation: 1

I had a similar issue when reading xlsx files. I changed my connection string to the following and it worked:

const string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel.xlsx;" +"Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"";

You need to convert your xls to xlsx and use 

Microsoft.ACE.OLEDB.12.0

to read the xlsx file.

Upvotes: 3

Shan
Shan

Reputation: 11

After few hours of trial and error and searching for the solution, I got working with this:

"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= {0}; Extended Properties=\"Excel 12.0;IMEX=2;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\""

Note:

  1. IMEX = 2
  2. HDR = YES, and most importantly
  3. Extended Properties should be enclosed with double quote

Sorry this is not working too...used wrong file :-(

Upvotes: 1

Rohit Agrawal
Rohit Agrawal

Reputation: 483

I guess your connection string is wrong. If the Excel version is 2007 or higher (i.e. .xlsx extension), then we need to use ACE engine else in case of xls file (in your case), we should use the Jet engine connection string. Please replace the below mentioned string with your existing one. I guess the problem should get solved.

mCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                  "Data Source=" + FilePath + ";" +
                  "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' ";

Upvotes: -1

Veer
Veer

Reputation: 1593

Add Importmixedtypes=text to your connecton string like this

mCon.ConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;Importmixedtypes=text;typeguessrows=0;\"",mstrFilePath);

Upvotes: 1

Related Questions