Search code examples
c#excelconnection-stringoledboledbconnection

OleDb Connection not reading all the rows from excel file

I am using an OleDb in C# connection to read data from an Excel sheet. And fill it in a Datatable.

The sheet contains 275 rows and 27 columns. After I read it, Rows 1,2 and 3 are empty. All the other rows are filled correctly.

Anyone have an idea on the problem?

Here is my code:

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + "C:/Sheets/DataSheet.xls" + ";Extended Properties=Excel 12.0;";

objConn = new OleDbConnection(connString);

string Query = "SELECT * FROM [Sheet1$]";
OleDbCommand objCmd = new OleDbCommand(Query, objConn);

DataTable Table = new DataTable();
objAdapter1.SelectCommand = objCmd;
objAdapter1.Fill(Table);
Solution

  • The problem was that my sheet contained mixed data and it was only reading numbers. The solution is to specify 

    Properties=\"Excel 12.0;IMEX=1\";"

    IMEX=1 allow the reader to import all data not only numbers