Efficient way of importing data from Excel to SQL

Question

I have VBA code for importing data from excel files to SQL as follow:



Sub create_table()

    Set myconn = New ADODB.Connection
    
    strConn = "Provider=SQLOLEDB;Data Source=some_server;Integrated Security=SSPI;Connect Timeout=300"
    myconn.Open strConn
    myconn.CommandTimeout = 500
    
    Set rs = CreateObject("ADODB.Recordset")
    
    'SQLStr = "drop table B.dbo.temp_table" ' usunięcie tabeli
    'rs.Open SQLStr, myconn, adOpenStatic

    SQLStr = "use B; create table B.dbo.temp_table" & _
             "(id numeric, data_1 datetime2, d2d decimal(8,2), gps decimal(8,2))"

    rs.Open SQLStr, myconn, adOpenStatic

    Set rs = Nothing
    myconn.Close
    Set myconn = Nothing

End Sub
Sub update_import_data()

    Set myconn = New ADODB.Connection
    
    strConn = "Provider=SQLOLEDB;Data Source=some_server;Integrated Security=SSPI;Connect Timeout=300"
    myconn.Open strConn
    myconn.CommandTimeout = 1000
    
    Set rs = CreateObject("ADODB.Recordset")
    
    SQLStr = "delete from B.dbo.temp_table" 
    rs.Open SQLStr, myconn, adOpenStatic
    
                
    For i = 1 To Sheets("przychod").Range("A1").End(xlDown).Row - 1

    umowa = Sheets("przychod").Cells(i + 1, 2).Value
    miesiac = Format(Sheets("przychod").Cells(i + 1, 3).Value, "yyyymmdd")
    d2d = Replace(CStr(Sheets("przychod").Cells(i + 1, 4).Value), ",", ".")
    gps = Replace(CStr(Sheets("przychod").Cells(i + 1, 5).Value), ",", ".")
    
      SQLStr1 = " INSERT INTO DC.dbo.mz_przychod" & _
              "(id, data_1, d2d , gps) values (" & umowa & ", '" & miesiac & "', '" & d2d & "', '" & gps & "')"
    
     rs.Open SQLStr1, myconn, adOpenStatic
    
    Next i
    
    Set rs = Nothing
    myconn.Close
    Set myconn = Nothing

End Sub

and that code works fine for some data, but right now I have to import almost 200k of rows and it calculates for so long to import that data, therefore I'm looking for faster method. I cannot use SSMS import wizard.

Do u know faster method? Can I within VBA convert whole column (range) using some arrays method or do I need to go by each item with a for loop?

You could try opening an updatable recordset and populating records that way: eg see https://stackoverflow.com/a/44959630/478884 Call the `UpdateBatch` every 1000 records or so. — Tim Williams, Oct 06 '22 at 16:20

Efficient way of importing data from Excel to SQL

0 Answers0