Bulk Import from CSV to SQL Server using Excel VBA ADODB

adodb, csv, excel, t-sql, vba

Solution

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

Problem

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 ```

Original source