OleDb Connection not reading all the rows from excel file

Question

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);

Even I encountered such problems. You can refer : http://stackoverflow.com/questions/6913497/unknown-problem-while-exporting-excel-to-system-datatable and http://stackoverflow.com/questions/1941083/problems-reading-in-an-excel-file-in-c also this http://stackoverflow.com/questions/6913497/unknown-problem-while-exporting-excel-to-system-datatable Make Sure your connections are proper http://www.connectionstrings.com/excel — Pratik, Sep 07 '11 at 13:49

score 6 · Accepted Answer · answered Sep 07 '11 at 07:53

6

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

answered Sep 07 '11 at 07:53

Y2theZ

10,162
38
131
200

score 2 · Answer 2 · edited May 23 '17 at 11:47

2

This post has some information on the first row being treated as a header, unless you specify HDR=NO in the connection string.

I am not sure why the 2nd and 3rd rows might be skipped however; is there any difference in their contents that you can see?

edited May 23 '17 at 11:47

Community

1
1

answered Sep 06 '11 at 14:27

Daniel B

2,877
18
18

Thank you for your answer. But I know that the first row is a header and did not count it. it is filling the header correctly but the next 3 rows are not filled. And no there is no difference in their content – Y2theZ Sep 06 '11 at 14:55

score 0 · Answer 3 · answered Oct 03 '17 at 12:07

0

I just added new row to upper row and it appears in dataset. So rows orders changing solved problem in my case

answered Oct 03 '17 at 12:07

alexey

783
1
7
19

OleDb Connection not reading all the rows from excel file

3 Answers3

Linked