OleDbDataAdapter not filling all of the rows

Question

Hey I am using the DataAdapter to read an excel file and fill a Data Table with that data.

Here is my query and connection string.

private string Query = "SELECT * FROM Sheet1";
private string ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;"
                                    + "Data Source=\"" + Location + "\";"
                                    + "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

OleDbDataAdapter DBAddapter = new OleDbDataAdapter(Query, ConnectString);
DataTable DBTable = new DataTable();
DBAddapter.Fill(DBTable);

The problem is my excel file has 12000 records however its only filling 2502 records into my data table.

Is there a limit on how many records the data adapter can read and write to the data table?

Are you sure the `Sheet1` contains `12000` records? The `DataTable` may have some limit but not that small. Could you try replacing `Sheet1` by `Sheet1$`? — King King, Aug 21 '13 at 19:14
I thought you have to surround the sheetname with "[...]" and of course add $ to the sheetname like King King said — Daniel Abou Chleih, Aug 21 '13 at 19:20
that's not how I add the sheet name in the query. It gets the right sheet name because I am getting some of the data in. I think because I copy and pasted the data into the excel file its probably not formatted right to for the oleDBAdapter to read it possibly. I use the sheet name like this in my actual code. ["+sheetNames[0]+"] — Mitch, Aug 21 '13 at 19:24

score 2 · Answer 1 · edited Mar 18 '16 at 07:58

2

The problem might be that the sheet would contain mixed data and it was only reading numbers. The solution is to specify:

Properties="Excel 12.0;IMEX=1";

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

edited Mar 18 '16 at 07:58

チーズパン

2,752
8
42
63

answered Mar 18 '16 at 07:07

Vinodh Gandhi

21
2

OleDbDataAdapter not filling all of the rows

1 Answers1

Linked