Excel VBA: How to import excel files into MS SQL table? (batch upload, not one record at a time)

VAer 756 Reputation points

Here is basic code structure, which works, but it is very very time consuming (if there are a lot of data in excel file).

How should I re-write the whole program and make it more efficient?



Private Sub CommandButtonTestScoreUpload_Click()  
Dim WB As Workbook  
Dim WS As Worksheet  
Dim ShtName As String  
Dim i, j As Long  
Dim WBLastRow As Long  
Dim UploadTestScoreQuery As String  
Dim TestScoreRs As ADODB.Recordset  
Dim sFile As String  
Dim InsertNotUpdate As Boolean  
sFile = Application.GetOpenFilename("*.xl*,*.xl*", , "Select Test Score Excel file")  
If sFile <> "False" Then  
    Set WB = Workbooks.Open(sFile)  
    ShtName = WB.Sheets(1).Name  
    WBLastRow = WB.Sheets(ShtName).Cells(Rows.Count, "A").End(xlUp).Row  
    Set CnDev = CreateObject("ADODB.Connection")  
    CnDev.Open ConnectionString  
    For i = 2 To WBLastRow  
        InsertNotUpdate = True  
        'I have If...Then... statement here, if a record already exists in Database table, then set InsertNotUpdate = False  
        'Basically, I download whole MS SQL table into another new workbook, then compare excel record against downloaded table.  
        If InsertNotUpdate = True Then  'Inert new record  
            UploadTestScoreQuery = "INSERT INTO Test_score ......"   'INSERT SQL Statement  
        Else  'Update existing record  
            UploadTestScoreQuery = "UPDATE Test_score SET ........."   'UPDATE SQL statement  
        End If  
        Set TestScoreRs = CreateObject("ADODB.Recordset")  
        TestScoreRs.Open UploadTestScoreQuery, CnDev  
        Set TestScoreRs = Nothing  
    Next i  
End If  
Application.DisplayAlerts = False  
WB.Close savechanges:=False  
Application.DisplayAlerts = True  
Set CnDev = Nothing  
MsgBox "We have finished uploading your data."  
End Sub  
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Tom van Stiphout 1,606 Reputation points MVP

    SSIS = SQL Server Integration Services
    This can import Excel files better than your RBAR.