vba 如何将excel表中的数据插入到数据库表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1570387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert data from an excel sheet into a database table?
提问by SrinR
I need to insert the data from an excel sheet into a teradata table. and I need this to be done using a MACRO.
我需要将 Excel 工作表中的数据插入到 teradata 表中。我需要使用宏来完成这项工作。
I have data in an excel sheet like
我在 Excel 表中有数据，例如
COL1 COL2 COL3 COL4 1 2 3 4 2 5 8 10 . .很快
and i need to keep a button in the excel sheet and assign a macro to that button so that when i click the button the rows in the excel sheet should be inserted into a database table.
我需要在 Excel 工作表中保留一个按钮并为该按钮分配一个宏，以便当我单击该按钮时，Excel 工作表中的行应插入到数据库表中。
The requirement is that I will send the empty excel sheet to the person, he will fill in the sheet with the data and he clicks the button in the excel and the data has to be inserted into the database table. I would prefer doing this using a macro..
要求是我将空的excel表发送给该人，他将用数据填充该表，然后单击excel中的按钮，数据必须插入到数据库表中。我更喜欢使用宏来做到这一点..
Thanks all.
谢谢大家。
回答by Petrik
I've created function that will transform the Excel table into multiple Insert Commands.
我创建了将 Excel 表格转换为多个插入命令的函数。
Copy this into the module and then in formula, set up as first parameter the values of cells that needs to be inserted, the second range should be the names of columns (press F4 to set up this as constant), and third (optional) the name of the table. If table name is not specified, then the name of sheet will be used as default.
将此复制到模块中，然后在公式中，将需要插入的单元格的值设置为第一个参数，第二个范围应为列名（按F4将其设置为常量），第三个（可选）表的名称。如果未指定表名，则默认使用工作表名称。
In your case this is how the spreadsheet should look like:
在您的情况下，电子表格应如下所示：
+---+------+------+------+------+-----------------------------------------+
| | A | B | C | D | E |
+---+------+------+------+------+-----------------------------------------+
| 1 | COL1 | COL2 | COL3 | COL4 | |
+---+------+------+------+------+-----------------------------------------+
| 2 | 1 | 2 | 3 | 4 | =Insert2DB(A2:D2,$A:$D,"TableName") |
+---+------+------+------+------+-----------------------------------------+
| 3 | 2 | 5 | 8 | 10 | =Insert2DB(A3:D3,$A:$D,"TableName") |
+---+------+------+------+------+-----------------------------------------+
This will generate for you those two queries:
这将为您生成这两个查询：
INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (1,2,3,4)
INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (2,5,8,10)
Here is the function (works good with Microsoft SQL (TSQL):
这是函数（适用于 Microsoft 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
If you have quite a lot data to insert, you might not need to use INSERT INTO in each command, then just use the Insert2DB function in first row (and every 500th) and for the rest use just Insert2DBValues:
如果您有大量数据要插入，您可能不需要在每个命令中使用 INSERT INTO，然后只需在第一行（以及每 500 个）中使用 Insert2DB 函数，其余的只使用 Insert2DBValues：
+---+------+------+------+------+-----------------------------------------------+
| | A | B | C | D | E |
+---+------+------+------+------+-----------------------------------------------+
| 1 | COL1 | COL2 | COL3 | COL4 | |
+---+------+------+------+------+-----------------------------------------------+
| 2 | 1 | 2 | 3 | 4 | =Insert2DB(B3:E3,$B:$E,"TableName") |
+---+------+------+------+------+-----------------------------------------------+
| 3 | 2 | 5 | 8 | 10 | =Insert2DBValues(A3:D3,$A:$D,"TableName") |
+---+------+------+------+------+-----------------------------------------------+
This will give you following commands:
这将为您提供以下命令：
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
And finally, if you are using MySQL, there is different escaping of strings, so in such a case use Insert2DBMySQL:
最后，如果你使用 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
