将Excel导入Sqlserver表 [英] Import Excel into Sqlserver table

问题描述

嗨Dude，

我将excel表导入sqlserver表，excel有130个coulmns，我想只导入20列。 sqlserver表应该替换为新记录而旧的记录应该被删除，但旧的一个应该在导入错误或一些粉碎时出现。

i已经完成导入excel到sqlserver但是不能做sqlserver端。

我刚创建了表格并将数据从excel导入表格。

请找到我用过的代码： 1）我是在UI中使用文件上传和提交按钮。

 受保护  void  submit_Click（ object  sender，EventArgs e）{
  string  excelConnectionString; 
  string  ExcelContentType =   application / octet-流; 
  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）; 
 fup1.SaveAs（path）; 
  if （Path.GetExtension（fup1.FileName）==   .xls || Path.GetExtension（fup1.FileName）==  。 XLSX）
 {excelConnectionString =  string  .Format（ @  Provider = Microsoft.Jet.Oledb.4.0; Data Source = + path +  ;扩展属性= Excel 8.0;）; 
}  else  {
 excelConnectionString =  string  .Format（ @  Provider = Microsoft.ACE.Oledb.12.0; Data Source = + path +  ;扩展属性= Excel 12.0;）; 
} 
 使用（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 $]，连接）; 
 DtSet =  new  System.Data.DataSet（）; 
 MyCommand.Fill（DtSet）; 
 dt = DtSet.Tables [ 0 ]; 
  string  con1 = dataManager.ConnectionString.ToString（）; 
 使用（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 ， 舞台）; 
 bulkCopy.ColumnMappings.Add（ 23 ， 受限）; 
 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 ， 级别1\" ）; 
 bulkCopy.ColumnMappings.Add（ 118 ， 级别2\" ）; 
 bulkCopy.ColumnMappings.Add（ 121 ，  Net_Revenue）; 
 bulkCopy.DestinationTableName =   tblmobilityimport; 
 bulkCopy.BatchSize = dt.Rows.Count; 
 bulkCopy.WriteToServer（dt）; 
 bulkCopy.Close（）; 
}} 
}  catch （Exception ex）}}}} 

解决方案

，connection）;
DtSet = new System.Data.DataSet（）;
MyCommand.Fill（DtSet）;
dt = DtSet.Tables [ 0 ];
string con1 = dataManager.ConnectionString.ToString（）;
使用（SqlBulkCopy bulkCopy = new SqlBulkCopy（con1））
{bulkCopy.ColumnMappings.Add（ 3 ， 机会_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 ， 舞台）;
bulkCopy.ColumnMappings.Add（ 23 ， 受限）;
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 ， 级别1\" ）;
bulkCopy.ColumnMappings.Add（ 118 ， 级别2\" ）;
bulkCopy.ColumnMappings.Add（ 121 ， Net_Revenue）;
bulkCopy.DestinationTableName = tblmobilityimport;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer（dt）;
bulkCopy.Close（）;
}}
} catch （Exception ex）}}}}

< blockquote>如果打算恢复导出失败，你可以使用交易。



参考以下链接



了解ADO.NET中的事务和TransactionScope的初学者教程 [< a href =http://www.codeproject.com/Articles/522039/A-Beginners-Tutorial-for-Understanding-Transactiontarget =_ blanktitle =New Window> ^ ]



希望这有帮助...

您好，查看链接



http://www.dotnetpools.com/2013/01 /how-to-import-excelsheet-data-into-sql.html [ ^ ]

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);
                    fup1.SaveAs(path);
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();
                            MyCommand.Fill(DtSet);
                            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;
                                    bulkCopy.WriteToServer(dt);
                                    bulkCopy.Close();
                                }           }
                    }catch (Exception  ex)                                      }                }            }        }

解决方案

", connection); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); 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; bulkCopy.WriteToServer(dt); bulkCopy.Close(); } } }catch (Exception ex) } } } }

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...

Hi check the link

http://www.dotnetpools.com/2013/01/how-to-import-excelsheet-data-into-sql.html[^]

这篇关于将Excel导入Sqlserver表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！