OleDbDataAdapter not filling all of the rows

问题:

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?

回答1:

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.