Bulk Import from CSV to SQL Server using Excel VBA ADODB

Question

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.

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

If performance is your concern, why do you need to do this in Excel? How many rows are you talking about and how fast is fast enough? — NYCdotNet
– NYCdotNet, Commented Jul 13, 2014 at 10:01
@NYCdotNet, data seems to originate in Excel, and is exported to csv file simply to do the bulk import. — Rachel Hettinger
– Rachel Hettinger, Commented Jul 14, 2014 at 16:50
@"randomdownvoter" Why the down vote? Down votes should be accompanied by an explanation, especially for someone asking their first question. — Rachel Hettinger
– Rachel Hettinger, Commented Jul 14, 2014 at 16:54

Rachel Hettinger · Accepted Answer · 2014-07-14 16:58:17Z

2

You need to use BULK INSERT rather than INSERT INTO. Try something like this:

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

answered Jul 14, 2014 at 16:58

Rachel Hettinger

8,4872 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bulk Import from CSV to SQL Server using Excel VBA ADODB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related