会数据库导入的人这么少嘛?

Question

我已有一个数据库A中里面一张表B
B表中有多个字段   
后来还需要大量的数据 领导发了一个Execl数据 Execl里面是其他的几个字段
现在我想把Execl里面的字段加入到A库中的B表里，这个不是很会，请大家帮忙有什么好方法嘛?

Answer 1

先将Excel转换成Access文件，然后通过SQL Server里自带的数据导入导出功能就行了。不过首先你得保证表中字段和Excel中字段顺序是一一对应的即可。

Answer 2

Excel
可以直接导入呀
设置好表头就可以了呀

Answer 3

我试过 也能将Excel 数据导入到已有表的数据中, 但是变样了 
 id    name     
 1      北京
 2      天津
 null   null   1234  北京 
 null   null   5678  天津  
 

Answer 4

    导入 说白了 不就是 批量插入嘛。
可以很繁杂 也 可以很简单，无非是业务上的处理。
技术含量，我个人觉得不是很大。（莫要拿性能说事）.
写个小的程序，先读Excel, 转换成 Datatable, 循环这个table, 
拼接SQL 插入语句。执行，需要事物处理的加处理。

Answer 5

 public static bool OperateDatasWithTransaction(List<string> sqlList)
        {
            SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;
            // Start a local transaction
            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                foreach (string item in sqlList)
                {
                    myCommand.CommandText = item;
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                return true;
            }
            catch (Exception)
            {
                myTrans.Rollback();
            }
            finally
            {
                myConnection.Close();
            }
            return false;
        }
        private System.Data.DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds = new DataSet();
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }            if (ds == null)
            {
                return null;
            }            if (ds.Tables.Count < 1)
            {
                return null;
            }            return ds.Tables[0];
        }

Answer 6

 protected void AddExcel(DataSet ds, ref bool s)
        {
            try
            {
                 System.Data.DataTable dt = ds.Tables[0];              // string fileName ="C:\\数据处理文件夹\\"+ Guid.NewGuid() + ".xls";
                 //string str = DateTime.Now.ToShortDateString();
               // string[] strar=str.Split(' ');
               // string str1 =strar[0]+strar[1]; 
                 SaveFileDialog sf = new SaveFileDialog();
                 sf.InitialDirectory = "C:\\";
                 sf.Filter = "excel文件（*.xls）|*.xls";
                 
               if (sf.ShowDialog()==DialogResult.OK)
              {
                  string fileName = sf.FileName; //"C:\\数据处理文件夹\\" +str+ ".xls";
                Excel.Application excel = new Excel.ApplicationClass();                int rowIndex = 1;
                int colIndex = 0;                excel.Application.Workbooks.Add(true);                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }                foreach (DataRow row in dt.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                    {
                        excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
                    }
                }                excel.Visible = false;
               // excel.ActiveWorkbook.s
                excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                //excel.Save(fileName);                 excel.Quit();
                excel = null;                GC.Collect();//垃圾回收
            }
            }
            catch
            {
                s = false;
            }
       // }        }

Answer 7

//导入函数
private bool input(System.Data.DataTable dt, SqlConnection conn, bool success,string flag)
        {
            if (dt != null)
            {
                try
                {                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    List<string> L = new List<string>();
                    if (flag == "Budget")
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i].ItemArray[0].ToString() != "")
                            {
                                string s = "insert into [Budget] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "')";
                                //  OleDbCommand cmd = new OleDbCommand( s,conn);
                                L.Add(s);
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i].ItemArray[0].ToString() != "")
                            {
                                string s = "insert into [Budget_Detail] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "','" + dt.Rows[i].ItemArray[4] + "','" + dt.Rows[i].ItemArray[5] + "','" + dt.Rows[i].ItemArray[6] + "','" + dt.Rows[i].ItemArray[9] + "')";
                                //  OleDbCommand cmd = new OleDbCommand( s,conn);
                                L.Add(s);
                            }
                        }
                    }                    if (!OperateDatasWithTransaction(L))
                    {
                        success = false;
                    }                }
                catch (Exception eer)
                {
                    MessageBox.Show(eer.ToString());
                    success = false;
                    conn.Close();
                }
                if (success)
                {
                    label1.Text = "导入成功";
                    if (flag == "Budget")
                        MessageBox.Show("预算资金总账导入成功！", "信息提示");
                    else
                        MessageBox.Show("预算资金明细导入成功!", "信息提示");                }
                else
                {
                    label1.Text = "导入失败";
                    if (flag == "Budget")
                        MessageBox.Show("预算资金总账导入失败！", "信息提示");
                    else
                        MessageBox.Show("预算资金明细导入失败!", "信息提示");
                }
            }
            return success;
        }

Answer 8

整表复制进数据库再处理嘛！
参考：/*------导入Excel数据-----*/
--设置
SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE
GO
SP_CONFIGURE 'show advanced options',0
GO
RECONFIGURE
GO--导入
select * into TB from OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
  'Excel 8.0;HDR=YES;DATABASE=D:\xuri\RoleItem.xls',Sheet1$);

Answer 9

   Dim connstr As String = IRIS.Common.Common.getAppSetting("DTSConnectionString")
        Dim conn As SqlConnection = New SqlConnection(connstr)
        Dim strpath As String = fleUpload.PostedFile.FileName
        If strpath = "" Then
            Response.Write("<script>alert('Please select  file path !')</script>")
            Response.End()
        End If
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strpath & "; Extended Properties=Excel 8.0;"
        Dim oleDbConnection As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Try
            oleDbConnection.Open()
            conn.Open()
            Dim sheetdataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim tableName As String = sheetdataTable.Rows(3)(2).ToString.Trim()
            tableName = "[" & tableName.Replace(" ' ", " ") & "]"
            Dim query As String = "select Country,Comp_ID,BankName,Facility,Role,ParticipationAmt,Curr,Amount,CurrAlt,AmountAlt,IssueDate,MaturityDate,CloseOutDate,ContNo,TranType,BUnit,TMISBankCode from" & tableName
            Dim exceldataset As DataSet = New DataSet()
            Dim exceloleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
            exceloleAdapter.Fill(exceldataset, "intUploadFacility")            Dim dataTable As DataTable = New DataTable()
            Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("select UploadFacilityID,UserID,Facility,Curr,TotalAmount,MaturityDate,IssueDate,Curr_Alt,TotalAmount_Alt,Comp_ID,Role,ParticipationAmt,IntStatus,ContNo from intUploadFacility", conn)
            Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)
            sqlDA1.Fill(dataTable)            Dim exceldataRow As DataRow
            Dim UploadFacilityID As Int32 = 0
            For Each exceldataRow In exceldataset.Tables("intUploadFacility").Rows
                Dim dataRow As DataRow = dataTable.NewRow()
                UploadFacilityID = UploadFacilityID + 1
                dataRow("UploadFacilityID") = UploadFacilityID                dataRow("UserID") = Me.UserID
                dataRow("Facility") = exceldataRow("Facility")
                dataRow("Curr") = exceldataRow("Curr")
                dataRow("TotalAmount") = exceldataRow("Amount")
                dataRow("MaturityDate") = exceldataRow("MaturityDate")
                dataRow("IssueDate") = exceldataRow("IssueDate")                If Not exceldataRow("CurrAlt") Is DBNull.Value Then
                    dataRow("Curr_Alt") = exceldataRow("CurrAlt")
                Else
                    dataRow("Curr_Alt") = ""
                End If
                If Not exceldataRow("AmountAlt") Is DBNull.Value Then
                    dataRow("TotalAmount_Alt") = Convert.ToDouble(exceldataRow("AmountAlt"))
                Else
                    dataRow("TotalAmount_Alt") = 0
                End If                If Not exceldataRow("Comp_ID") Is DBNull.Value Then
                    dataRow("Comp_ID") = exceldataRow("Comp_ID")
                Else
                    dataRow("Comp_ID") = 0
                End If                dataRow("Role") = exceldataRow("Role")
                ' Dim particpationAmt As String = Convert.ToString(dataRow11("ParticipationAmt") + "")                If Not exceldataRow("ParticipationAmt") Is DBNull.Value Then
                    dataRow("ParticipationAmt") = Convert.ToDouble(exceldataRow("ParticipationAmt"))
                Else
                    dataRow("ParticipationAmt") = 0
                End If
                dataRow("IntStatus") = ""
                dataRow("ContNo") = exceldataRow("ContNo")
                dataTable.Rows.Add(dataRow)
            Next       
        
            sqlDA1.Update(dataTable)    
            Return True
        Catch ex As Exception            Page.RegisterStartupScript("warning", "<script language=""javascript"">window.alert(""File format is not correct.\nPlease check the file you selected!\nPlease contact the system admin!"");</script>")
            Return False
        Finally
            sConnectionString.Clone()
            conn.Close()
            oleDbConnection.Close()
        End Try

Answer 10

分少！
EXCEL到数据很方便，设置好位置、表头，确定里面数据无异常无冲突数据，直接导，简单的很，你可以GOOGLE下

Answer 11

把EXCEL的列名和数据表的顺序调到一致，如：
数据表：A1，A2
EXCEL：A1，A2
然后从EXCEL中复制除标题行的所有数据
打开数据表
点击插入行的右击，先粘贴即可。