Click here to Skip to main content
14,694,530 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Dude,
I'm importing excel sheet into sqlserver table,excel is having 130 coulmns and i wanna import only 20 columns. sqlserver table should be replaced with new records and old one wanna deleted,but old one should be there when import failes or some crushes occur.
i have completed import excel into sqlserver but not able to do the sqlserver side.
Just i have created table and importing data's into table from excel.
Please find the code i have used:1)i'm using file upload and submit button in UI.

 protected void submit_Click(object sender, EventArgs e)       {
            string excelConnectionString;
            string ExcelContentType = "application/octet-stream";
    string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            if (fup1.HasFile)            {
      if (fup1.PostedFile.ContentType == ExcelContentType || fup1.PostedFile.ContentType == Excel2010ContentType)
                {try        {
string path = string.Concat(Server.MapPath("~/TempFiles/"), fup1.FileName);
if (Path.GetExtension(fup1.FileName) == ".xls" || Path.GetExtension(fup1.FileName) == ".XLSX")
{ excelConnectionString = string.Format(@"Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + path + "; Extended Properties=Excel 8.0;");
              }  else              {
excelConnectionString = string.Format(@"Provider=Microsoft.ACE.Oledb.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0;");
       using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                            DataSet DtSet = null;
                            DataTable dt = new DataTable();
                            DataTable dt1=new DataTable();
                            OleDbDataAdapter MyCommand = null;                            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [MobilityOM$]", connection);
                            DtSet = new System.Data.DataSet();
                            dt = DtSet.Tables[0];
                                string con1 = dataManager.ConnectionString.ToString();
                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con1))
                                {       bulkCopy.ColumnMappings.Add(3, "Opportunity_ID");
                                    bulkCopy.ColumnMappings.Add(4, "Reporting_Status");
                                    bulkCopy.ColumnMappings.Add(5, "OG");
                                    bulkCopy.ColumnMappings.Add(9, "Geo_Area");
                                    bulkCopy.ColumnMappings.Add(10, "Geo_Unit");
                                    bulkCopy.ColumnMappings.Add(11, "Master_Client_Name");
                                    bulkCopy.ColumnMappings.Add(12, "Master_Client_Class");
                                    bulkCopy.ColumnMappings.Add(16, "CR_Name");
                                    bulkCopy.ColumnMappings.Add(19, "Opportunity_Class");
                                    bulkCopy.ColumnMappings.Add(20, "Stage");
                                    bulkCopy.ColumnMappings.Add(23, "Restricted");
                                    bulkCopy.ColumnMappings.Add(24, "Create_Date");
                                    bulkCopy.ColumnMappings.Add(27, "Proposal_Submission_Date");
                          bulkCopy.ColumnMappings.Add(29, "Expected_Contract_Signing_Date");
                                    bulkCopy.ColumnMappings.Add(31, "Status_Since_Date");
                                    bulkCopy.ColumnMappings.Add(42, "Total_Net_Revenue");
                                    bulkCopy.ColumnMappings.Add(45, "Win_Probability");
                                    bulkCopy.ColumnMappings.Add(117, "Level1");
                                    bulkCopy.ColumnMappings.Add(118, "Level2");
                                    bulkCopy.ColumnMappings.Add(121, "Net_Revenue");
                                    bulkCopy.DestinationTableName = "tblmobilityimport";
                                    bulkCopy.BatchSize = dt.Rows.Count;
                                }           }
                    }catch (Exception  ex)                                      }                }            }        }
Updated 13-Nov-13 19:03pm

You can use transactions if intend to revert on export failure .

Refer the below link

A Beginner's Tutorial for Understanding Transactions and TransactionScope in ADO.NET[^]

Hope this helps...

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