CODEWITHSUNDEEP
excelvbat-sqlcsvadodb
macarius
macarius

Reputation: 53

Bulk Import from CSV to SQL Server using Excel VBA ADODB

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

Upvotes: 4

Views: 12393

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

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

Upvotes: 2

Related Questions