CODEWITHSUNDEEP
c#excelconnection-stringoledb
devrox
devrox

Reputation: 110

IMEX in OleDbConnection

I'm trying to read an excel file into a DataTable but IMEX driver does not read all the data in the column. What is the problem in this?

OleDbConnection dCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=HDR=YES;Excel 12.0;IMEX=1");

Here is the code.

OleDbConnection dCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            fileName + ";Extended Properties=HDR=YES;Excel 12.0;IMEX=1");

OleDbDataAdapter dAdp = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dCon);
dAdp.TableMappings.Add("tbl", "Table");
dAdp.Fill(dSet);
DataTable dT = dSet.Tables[0];

for (int i = 0; i < dT.Rows.Count; i++)
{
     //code for getting the values.
}

Upvotes: 0

Views: 1555

Answers (2)

Carth
Carth

Reputation: 2343

I realize this is a bit late for the OP but since I came across it I wanted to add that I believe the Extended Properties section is malformed in this example. Extended Properties needs to be separately enclosed in its own quotes in order to be processed appropriately.

The OPs example used this

OleDbConnection dCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        fileName + ";Extended Properties=HDR=YES;Excel 12.0;IMEX=1");

I believe this should have been

OleDbConnection dCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        fileName + ";Extended Properties=\"HDR=YES;Excel 12.0;IMEX=1\"");

With this malformed connection it's possible that the IMEX attribute was not being recognized.

See this MS article for information on possible connection string syntax options including those for Extended Properties.

Upvotes: 0

Derek
Derek

Reputation: 8628

The problem is that there's a limit to 255 Characters doing it this way.

You will maybe need to look at using the Microsoft.Office.Interop.Excel assembly for a better solution.

http://www.dotnetperls.com/excel

Upvotes: 1

Related Questions