将数据从Excel导入SQL Server [英] Import Data from Excel to SQL Server

问题描述

Hello All



我正在使用c＃windows表单应用程序。它要求我通过应用程序将数据从excel表导入到sql server。什么是优化的方法来做同样的事情？



但是，在我从excel插入数据之前，我需要检查表中是否存在重复记录。如果它已经存在，我会忽略该行并继续插入唯一的行。



任何指针都会非常感激。

< br $>


谢谢

Hello All

I am working on a c# windows form application. It requires me to import data from excel sheet to sql server through the application. What would be an optimized way to do the same?

However, before I insert the data from excel I need check check whether a duplicate record exists in the table or not. If it already exists, I ignore that row and proceed ahead with insertion of unique rows.

Any pointers would be greatly appreciated.


Thanks

推荐答案

我建​​议您批量插入临时表然后使其唯一（和其他在数据库上检查你是否喜欢。我认为这种技术既快又安全。



BULK INSERT [ ^ ]



，请看这里： http://stackoverflow.com/questions/13124680/how-to-bulk-insert-from-xlsx-file-extension [ ^ ]

I advice you to bulk insert into a temporary table then make it unique (and other checks if you like) on the database. I think this technique both faster and safer.

BULK INSERT[^]

and see here: http://stackoverflow.com/questions/13124680/how-to-bulk-insert-from-xlsx-file-extension[^]

好去...

http://support.microsoft.com/kb/302084 [ ^ ]



http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet .aspx [ ^ ]

Good to go...
http://support.microsoft.com/kb/302084[^]

http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx[^]

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
    string conStr="";
    switch (Extension)
    {
        case ".xls": //Excel 97-03
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                     .ConnectionString;
            break;
        case ".xlsx": //Excel 07
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                      .ConnectionString;
            break;
    }
    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;
 
    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();
 
    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();
 
    //Bind Data to GridView
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

简单的解决方法：

- >只需声明并填写数据表

- >通过检查重复项逐个插入记录

- >如果记录不存在，请转到并添加到数据表

- >如果记录存在，请离开并转到下一步

- >你可以将数据表绑定到SQL表

Simple workaround:
-> Simply declare and fill a datatable
-> insert one by one record by checking the duplicates
--> If record not exists, go and add to datatable
--> If record exists, leave and go to Next
-> you can bind the datatable to SQL table

这是一篇非常有趣的文章：使用C＃将MS Excel数据导入SQL Server表 [ ^ ]。根据您的需要编写代码;）

Here is very interesting article: Import MS Excel data to SQL Server table using C#[^]. Chnage the code to your needs ;)

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