|
I am attempting to import an Excel Spreadsheet (2010, XLSX) to Gridview, then upload to MS SQL 2012 database. There are 30,000 rows in spread sheet approximately. When I run it locally it works just fine. When I put it live on the server it doesn't work properly.
When it is importing into the Gridview it stops at a particular row every time. The row is around 21,000. If I delete the top 10,000 rows from spreadsheet it still stops at that same row. So in other words it doesn't matter if I try 21,000 rows or 11,000 rows it stops at that same one. That tells me it isn't a size or time limit. If I delete the top 16,000 rows and the bottom 5,000 rows. It does go past that record. This tells me the next record doesn't have bad data.
So we know it isn't a size / time issue. We know it isn't corrupt data in the next row. We know the code runs fine locally, but not on server.
I have tried this using datasets and datatables and the results are the same.
Connection String:
string myPath1 = @""+Server.MapPath(".") + @"\"+TextBox3.Text;
string strConn = @"provider=microsoft.ace.oledb.12.0;extended properties=""excel 12.0;HDR=YES"";data source="+myPath1+"";
In web.config I have:
<httpRuntime maxRequestLength="72604736"/>
and
<requestLimits maxAllowedContentLength="72604736" />
GridView code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" >
</asp:GridView>
Code behind to fill GridView (DataTable)
OleDbConnection myExcelConn = new OleDbConnection(strConn);
OleDbCommand myExcellComm = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable myDT = new DataTable();
myExcellComm.Connection = myExcelConn;
myExcelConn.Open();
myExcellComm.CommandText = query;
oda.SelectCommand = myExcellComm;
oda.Fill(myDT);
myExcelConn.Close();
GridView1.DataSource = myDT;
GridView1.DataBind();
Code behind to fill GridView (DataSet) DataSet excelDataSet = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);
da.Fill(excelDataSet);
GridView1.DataSource = excelDataSet;
GridView1.DataBind();
Again doesn't matter if I use DataTable or DataSet it produces the same thing.
I have verified the spreadsheet that gets uploaded does contain all the data. I have tried using FTP instead of file upload and same results. I have downloaded the uploaded excel file and ran it locally and again it works just fine.
Using Visual Web Developer Express 2010 with all Service Packs and Updates in C# local machine - Vista Ultimate with all updates Web server - VPS Windows Server 2012 with all updates and MS SQL 2012 with all updates.
Does anyone have any idea and where I should look next to solve the problem?
|
|
|
|
|
I got it working.
Turns out you need to have IMEX=1 at the end of the connection string.
The complete working connection string will look like:
string strConn = @"provider=microsoft.ace.oledb.12.0;extended properties=""excel 12.0;HDR=YES;IMEX=1;"";data source="+myPath1+"";
|
|
|
|