Click here to Skip to main content
15,847,077 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

i am trying to import data from excel into sql server table.In local i can able to insert more than 7585 rows but when i deploy my web application to server side i can import only 7584 rows when i try to insert more than 7584 rows i get the following error.
please help me to fix this.

this is the code i use

C#
if (FileUpload1.HasFile)
                    {
                        string _strFileType = "", _strNewFileName = "", _strOLEDBConnection = "";
                        var fileName = Path.GetFileName(FileUpload1.FileName);

                        // get exstension of file
                        _strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();

                        int fileExtPos = fileName.LastIndexOf(".");

                        string newfilename = fileName.Substring(0, fileExtPos);
                        _strNewFileName = newfilename + "___" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss-ffff") + _strFileType;

                        // store the file inside ~/App_Data/uploads folder
                        string _strPath = Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName);
                        FileUpload1.SaveAs(_strPath);

                        string ListName = txtmpListName.Text;
                        string originalfilename = fileName;
                        string _strdate = DateTime.Now.ToString("yyyy-MM-dd");
                        string _strSQL = "";
                        ////Create a connection string to access the Excel file using the ACE provider.
                        ////This is for Excel 2007. 2003 uses an older driver.
                        //var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName)

+ ";Extended Properties=Excel 12.0;");
                        if (_strFileType.Trim() == ".xls")
                        {
                            _strOLEDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +

";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        else if (_strFileType.Trim() == ".xlsx")
                        {
                            _strOLEDBConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +

";Extended Properties=\"Excel 12.0;HDR=Yes\"";
                        }

                        //Fill the dataset with information from the BV and REV worksheet.
                        var adapterbvandrev = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", _strOLEDBConnection);
                        var dsbvandrev = new DataSet();
                        adapterbvandrev.Fill(dsbvandrev);
}


Error:
System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Elector.Content.ImportVoterList.btnSubmit_Click(Object sender, EventArgs e)
Posted
Updated 30-Aug-13 2:55am
v2
Comments
Vedangi 30-Aug-13 6:22am    
how you are fetching data from excel file ?
Herman<T>.Instance 30-Aug-13 7:19am    
difference in fields in a row used?
empty row?
etc...

There is no limit for 7584 rows in Excel. It was 65K in previous version Excel 2007 but this less its not.
It seems that the new row you insert has the delimitor conflicts so its not able to get beyond that.
Try to take another excel with some other data but more rows. Or simply copy existing data again within that sheet (just select 10 existing and append)

Else post your excel file.

There's nothing wrong or missing with your code
 
Share this answer
 
The error is not with your web application, but with your excel file. The record present at row number 7584/7585 needs some testing. Ensure data in these 2 records are consistent with other records. To debug the error in these records, you can delete the 1st 7582 records and try uploading again.

As mentioned in other solution, there is no such limit while reading data from excel file.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900