如何将Excel表格中的数据插入到数据库表格中？

问题描述：

我需要将Excel表格中的数据插入到Teradata表格中。和我需要这个使用MACRO来完成。如何将Excel表格中的数据插入到数据库表格中？

我有一个Excel工作表像

 
COL1 COL2 COL3 COL4 
1  2 3  4 
2  5 8  10 
. 
.

等

数据，我需要保持在Excel工作表的按钮和宏指定到该按钮，这样，当我点击按钮，在该行Excel表应该插入到数据库表中。

要求是我将发送空的excel表单给这个人，他会填写表单中的数据，他点击excel中的按钮，数据必须插入到数据库表中。我宁愿这样做使用宏。

谢谢大家。

答

看看this使用vb/vba代码（用于marco）将数据从excel移动到sql server的链接。

我得到的代码连接到SQLServer的。但我想为Teradata数据库做同样的事情.. – SrinivasR 2009-10-15 07:32:25

好吧，那么这个http://209.85.229.132/search?q=cache:LKwYRBskhUoJ:www.teradataforum.com/attachments/a040130a.rtf+Teradata+数据库+的excel和CD = 6＆HL = EN＆CT = clnk＆GL = ZA – 2009-10-15 07:55:34

答

我已经创建了将Excel表转换为多个插入命令的函数。

将其复制到模块中，然后在公式中将第一个参数设置为需要插入的单元格的值，第二个范围应该是列的名称（按F4将其设置为常量），并且第三（可选）表的名称。如果未指定表名称，则表格名称将被用作默认值。

在你的情况是这样的表格看起来应该像：

+---+------+------+------+------+-----------------------------------------+ 
| | A | B | C | D | E          | 
+---+------+------+------+------+-----------------------------------------+ 
| 1 | COL1 | COL2 | COL3 | COL4 |           | 
+---+------+------+------+------+-----------------------------------------+ 
| 2 | 1 | 2 | 3 | 4 | =Insert2DB(B3:E3,$B$2:$E$2,"TableName") | 
+---+------+------+------+------+-----------------------------------------+ 
| 3 | 2 | 5 | 8 | 10 | =Insert2DB(B4:E4,$B$2:$E$2,"TableName") | 
+---+------+------+------+------+-----------------------------------------+

这会为你生成这两个疑问：

INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (1,2,3,4) 
INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (2,5,8,10)

下面是函数（工作好与微软SQL（ TSQL）：

Function Insert2DB(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant) 

     Dim rangeCell As Range 
     Dim InsertValues As String 
     Dim CellValue As String 
     Dim C As Range 

     Dim AllColls As String 
     Dim SingleCell As Range 
     Dim TableColls As String 

    InsertValues = "" 

    'Start Loop 
    For Each rangeCell In InputRange.Cells 

    'Recognize data type 
    Set C = rangeCell 
     If IsEmpty(C) Then 
       'DataType is NULL then NULL 
       CellValue = "NULL" 
      ElseIf Application.IsText(C) Then 
       'DataType is VARCHAR or CHAR 
       CellValue = "'" & Trim(rangeCell.Value) & "'" 
      ElseIf Application.IsLogical(C) Then 
       'DataType is bit eg. TRUE/FALSE 
        If rangeCell.Value = True Then 
         CellValue = "1" 
        ElseIf rangeCell.Value = False Then 
         CellValue = "0" 
        End If 
      ElseIf Application.IsErr(C) Then 
       'If there is an ERROR in cell, the statment will return 0 
       CellValue = "NULL" 
      ElseIf IsDate(C) Then 
       'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm 
       CellValue = "'" & VBA.Format(rangeCell.Value, "yyyymmdd hh:mm:ss") & "'" 
      ElseIf InStr(1, C.Text, ":") <> 0 Then 
       'DataType is TIME 
       CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'" 
      ElseIf IsNumeric(C) Then 
       'DataType is number 
       CellValue = rangeCell.Value 
     End If 

    If (Len(InsertValues) > 0) Then 
     InsertValues = InsertValues + "," + CellValue 
    Else 
     InsertValues = CellValue 
    End If 

    Next rangeCell 
    'END Loop 

    If IsMissing(ColumnsNames) Then 
     TableColls = "" 
     Else 

     For Each SingleCell In ColumnsNames.Cells 
      If Len(AllColls) > 0 Then 
        AllColls = AllColls + "," + "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]" 
      Else 
        AllColls = "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]" 
      End If 
     Next SingleCell 
     TableColls = " (" & AllColls & ")" 
    End If 


    'If TableName is not set, then take the name of a sheet 
    If IsMissing(TableName) = True Then 
     TableName = ActiveSheet.Name 
    Else 
    TableName = TableName 
    End If 

    'Set the return value 
     Insert2DB = "INSERT INTO " & TableName & TableColls & " VALUES (" & InsertValues & ")" 

    End Function

如果您有很多要插入的数据，则可能不需要使用IN SERT INTO在每个命令，那么就使用第1行Insert2DB功能（每500次）和其余的使用只是Insert2DBValues：

+---+------+------+------+------+-----------------------------------------------+ 
| | A | B | C | D | E            | 
+---+------+------+------+------+-----------------------------------------------+ 
| 1 | COL1 | COL2 | COL3 | COL4 |            | 
+---+------+------+------+------+-----------------------------------------------+ 
| 2 | 1 | 2 | 3 | 4 | =Insert2DB(B3:E3,$B$2:$E$2,"TableName")  | 
+---+------+------+------+------+-----------------------------------------------+ 
| 3 | 2 | 5 | 8 | 10 | =Insert2DBValues(A3:D3,$A$1:$D$1,"TableName") | 
+---+------+------+------+------+-----------------------------------------------+

这会给你下面的命令：

INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (1,2,3,4) 
,(2,5,8,10) 


Function Insert2DBValues(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant) 

    Dim rangeCell As Range 
    Dim InsertValues As String 
    Dim CellValue As String 
    Dim C As Range 

    Dim AllColls As String 
    Dim SingleCell As Range 
    Dim TableColls As String 

InsertValues = "" 

'Start Loop 
For Each rangeCell In InputRange.Cells 

'Recognize data type 
Set C = rangeCell 
    If IsEmpty(C) Then 
      'DataType is NULL then NULL 
      CellValue = "NULL" 
     ElseIf Application.IsText(C) Then 
      'DataType is VARCHAR or CHAR 
      CellValue = "'" & Trim(rangeCell.Value) & "'" 
     ElseIf Application.IsLogical(C) Then 
      'DataType is bit eg. TRUE/FALSE 
       If rangeCell.Value = True Then 
        CellValue = "1" 
       ElseIf rangeCell.Value = False Then 
        CellValue = "0" 
       End If 
     ElseIf Application.IsErr(C) Then 
      'If there is an ERROR in cell, the statment will return 0 
      CellValue = "NULL" 
     ElseIf IsDate(C) Then 
      'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm 
      CellValue = "'" & VBA.Format(rangeCell.Value, "yyyy-mm-dd hh:mm:ss") & "'" 
     ElseIf InStr(1, C.Text, ":") <> 0 Then 
      'DataType is TIME 
      CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'" 
     ElseIf IsNumeric(C) Then 
      'DataType is number 
      CellValue = rangeCell.Value 
    End If 

If (Len(InsertValues) > 0) Then 
    InsertValues = InsertValues + "," + CellValue 
Else 
    InsertValues = CellValue 
End If 

Next rangeCell 
'END Loop 

If IsMissing(ColumnsNames) Then 
    TableColls = "" 
    Else 

    For Each SingleCell In ColumnsNames.Cells 
     If Len(AllColls) > 0 Then 
       AllColls = AllColls + "," + "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]" 
     Else 
       AllColls = "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]" 
     End If 
    Next SingleCell 
    TableColls = " (" & AllColls & ")" 
End If 


'If TableName is not set, then take the name of a sheet 
If IsMissing(TableName) = True Then 
    TableName = ActiveSheet.Name 
Else 
TableName = TableName 
End If 

'Set the return value 
    Insert2DBValues = ",(" & InsertValues & ")" 

End Function

而且最后，如果你正在使用MySQL，有弦的不同逃逸，所以在这种情况下使用Insert2DBMySQL：

Function Insert2DBMySQL(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant) 

     Dim rangeCell As Range 
     Dim InsertValues As String 
     Dim CellValue As String 
     Dim C As Range 

     Dim AllColls As String 
     Dim SingleCell As Range 
     Dim TableColls As String 

    InsertValues = "" 

    'Start Loop 
    For Each rangeCell In InputRange.Cells 

    'Recognize data type 
    Set C = rangeCell 
     If IsEmpty(C) Then 
       'DataType is NULL then NULL 
       CellValue = "NULL" 
      ElseIf Application.IsText(C) Then 
       'DataType is VARCHAR or CHAR 
       CellValue = "'" & Trim(rangeCell.Value) & "'" 
      ElseIf Application.IsLogical(C) Then 
       'DataType is bit eg. TRUE/FALSE 
        If rangeCell.Value = True Then 
         CellValue = "1" 
        ElseIf rangeCell.Value = False Then 
         CellValue = "0" 
        End If 
      ElseIf Application.IsErr(C) Then 
       'If there is an ERROR in cell, the statment will return 0 
       CellValue = "NULL" 
      ElseIf IsDate(C) Then 
       'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm 
       CellValue = "'" & VBA.Format(rangeCell.Value, "yyyy-mm-dd hh:mm:ss") & "'" 
      ElseIf InStr(1, C.Text, ":") <> 0 Then 
       'DataType is TIME 
       CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'" 
      ElseIf IsNumeric(C) Then 
       'DataType is number 
       CellValue = rangeCell.Value 
     End If 

    If (Len(InsertValues) > 0) Then 
     InsertValues = InsertValues + "," + CellValue 
    Else 
     InsertValues = CellValue 
    End If 

    Next rangeCell 
    'END Loop 

    If IsMissing(ColumnsNames) Then 
     TableColls = "" 
     Else 

     For Each SingleCell In ColumnsNames.Cells 
      If Len(AllColls) > 0 Then 
        AllColls = AllColls + "," + "" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "" 
      Else 
        AllColls = "" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "" 
      End If 
     Next SingleCell 
     TableColls = " (" & AllColls & ")" 
    End If 


    'If TableName is not set, then take the name of a sheet 
    If IsMissing(TableName) = True Then 
     TableName = ActiveSheet.Name 
    Else 
    TableName = TableName 
    End If 

    'Set the return value 
     Insert2DBMySQL = "INSERT INTO " & TableName & TableColls & " VALUES (" & InsertValues & ");" 

    End Function

如何将Excel表格中的数据插入到数据库表格中？

相关推荐