Last post May 27, 2013 08:18 AM by pabitrad
Jun 21, 2012 03:08 AM|Animesh-MS|LINK
I installed AccessDataBaseEngine.exe on Windows Server 2008 R2 (VM) and using it for reading excel files using Asp.net C#; I've an excel file which has almost 80000 records, but I am able to read only 8128 records from the excel sheet. I am using the following
connection string for reading records from excel sheet -
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=***UploadedExcelFilePath***;Extended Properties=Excel 12.0;"
Any ideas how to resolve this???
Jun 21, 2012 08:45 PM|Decker Dong - MSFT|LINK
but I am able to read only 8128 records from the excel sheet.
What about the rest ones？Any exceptions？
Jun 22, 2012 01:22 AM|Animesh-MS|LINK
No exception...while reading from the excel sheet (.xlsx file), i am able to read only 8000 or 10000 records; I tried to copy the data in separate excel sheet and then the record count increased little bit, like 19000, but the excel sheet has almost 80000
records. Is it a problem with AccessDataBaseEngine.exe or with the connection string??
Jun 22, 2012 04:53 AM|Decker Dong - MSFT|LINK
Would you mind trying to use OleDbDataAdapter.Fill into a DataTable to read out all the contents？
Jun 22, 2012 08:28 AM|Animesh-MS|LINK
I am using the following code to fill the dataset from excel sheet:
DataSet objDs = new DataSet();
using (OleDbConnection objCon = new OleDbConnection(strCon))
OleDbCommand objCmd = new OleDbCommand("Select * From [Sheet1$]", objCon);
OleDbDataAdapter objAdp = new OleDbDataAdapter(objCmd);
The datatable contains sometimes 8000, sometimes 19000 records of the excel sheet.
Jun 26, 2012 01:26 PM|Cathy Mi - MSFT|LINK
Have you tried adding IMEX=1 to the Extended Properties in the connection string?
In the Extended Properties, "IMEX=1;" tells the driver to always read "intermixed" data columns as text. It scans the first several rows of data (8 by default) to determine the data types of the columns. If it guesses a numeric data type and text is found
in later rows, they will be returned as null.
Jun 27, 2012 02:28 AM|Animesh-MS|LINK
Thanks for the reply. I tried adding, "IMEX=1;", but it gives the error,
"Exception occured while reading excel sheet records : System.Data.OleDb.OleDbException (0x80004005):
Could not find installable ISAM. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) "
Jun 27, 2012 03:45 AM|Animesh-MS|LINK
Finally, I am able to do read all the records from the excel sheet......i changed my connection string like the following (** adding IMEX=1 and keeping the extended properties in single quote **):
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ExcelFilePath;Extended Properties='Excel 12.0;HRD=YES; IMEX=1;'"
Thanks a lot, Cathy....You made my day!!!
May 27, 2013 08:18 AM|pabitrad|LINK
I am able to read 7400 recoed where as my xlsx file contains more than 65,000 records. I am using the following connection string , i,e, adding IMEX=1 and enclosed extended properties in single quote. Still no luck. Please help.