使用 Excel VBA ADODB 从 CSV 批量导入 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24717720/
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
Bulk Import from CSV to SQL Server using Excel VBA ADODB
提问by macarius
I am trying to import a large number of data from a CSV file to a SQL Sever database table. I am able to write line by line but that takes too long. What I have below fails on "FROM [C:\Temp\tblOPTExportData.csv]" during oComm.Execute. Any help would be appreciated.
我正在尝试将大量数据从 CSV 文件导入 SQL Sever 数据库表。我可以一行一行地写，但这需要太长时间。在 oComm.Execute 期间，我下面的内容在“FROM [C:\Temp\tblOPTExportData.csv]”上失败。任何帮助，将不胜感激。
On Error GoTo err_me
Dim locComm As New ADODB.Command
Dim locConnection As New ADODB.Connection
Dim locRst As New ADODB.Recordset
Dim ee As Boolean
Dim su As Boolean
Dim strSQLQuery As String
Dim shtDash As Worksheet
Dim shtData As Worksheet
Dim shtOP As Worksheet
With Application
ee = .EnableEvents
su = .ScreenUpdating
If ee Then .EnableEvents = False
If Not su Then .ScreenUpdating = True
End With
With ThisWorkbook
Set shtDash = .Sheets("Dashboard")
Set shtData = .Sheets("Data")
Set shtOP = .Sheets("OP")
End With
With locConnection
.CommandTimeout = 0
.ConnectionString = "Provider=SQLOLEDB;Server=sql-ewhcld-1000; Database=xxxxxxxxxxxxxx; User ID=tenant-xxxxxxxxxxxxxxx; Password=yeahidontthinkso; Trusted_Connection=True; Pooling=True; MultipleActiveResultSets=False"
.Open
End With
' ____________________________
' / \
' | IMS Factory Model Data |
' \____________________________/
'
'With statRng
' .Value = "Factory Model Phase Data // Importing"
' .Font.Color = 8421504
' .Characters(Start:=29, Length:=9).Font.Color = 10192433 'Blue
'End With
With shtOP
endRow = .Cells(.Rows.count, 2).End(xlUp).Row 'B (2)
End With
If endRow < 3 Then Err.Raise Number:=vbObjectError + 20002, Source:="exportData_Excel", Description:="No data found: 'OP' sheet, column 2 (B)."
If Not rangetoCSV("B3:K" & endRow, "tblOPTExportData", 201, , , "OP") Then Err.Raise Number:=vbObjectError + 30001, Description:="rangetoCSV, 'tblGates'"
strSQLQuery = "INSERT INTO optData (opsType, opsUID, opsDesc, opsProgram, opsFlight, opsProductAreaL1, opsAssignee, opsGenDate, opsECD, opsStatus) " & _
"SELECT Type, UID, Description, Program, Flight, L-1 IPT, Assignee, Generated, ECD, Status FROM [C:\Temp\tblOPTExportData.csv]"
With oComm
.ActiveConnection = locConnection
.CommandText = strSQLQuery
.Execute
End With
采纳答案by Rachel Hettinger
You need to use BULK INSERTrather than INSERT INTO. Try something like this:
您需要使用BULK INSERT而不是INSERT INTO. 尝试这样的事情：
strSQLQuery = "BULK INSERT optData " & _
"FROM C:\Temp\tblOPTExportData.csv " & _
"WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', " & _
"ROWTERMINATOR = '\n', TABLOCK)"
With oComm
.ActiveConnection = locConnection
.CommandType = adCmdText
.CommandText = strSQLQuery
.Execute
End With
