Update SQL Table from excel file with vb.net

Question

I've written a little programm which gets an .xlsx file in input (this file is periodically updated), and it extracts the data to an sql table. I would like to periodically update this sql table by comparing the content of the excel file with the content of sql table, and insert into it all the new rows, if they exist. I searched for many solutions with no success. How could I do?

My actual code looks like:

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceFile & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        ExcelConnection.Open()
        Dim RequeteExcelMat As String = "SELECT * FROM [feuil 1$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(RequeteExcelMat, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim ConnexionBDDMat As New SqlConnection("SERVER=(local);DATABASE=MatStat;Trusted_Connection=True")

        Try
            ConnexionBDDMat.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnexionBDDMat)
            bulkCopy.DestinationTableName = "dbo.Material"

            'If SourceFile Content different from "Material" Sql Table Content Then
            '   Update "Material" Table by inserting the new rows from SourceFile 
            'Else
            ' MsgBox("Table is already updated")
            'End If

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                ConnexionBDDMat.Close()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using

gofr1 · Accepted Answer · 2016-08-25 12:27:15Z

I use this to work with a small table to update/insert data from Excel to SQL Server

Sub merge()
    Dim CurConn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim SomeName As Worksheet
    Dim Lastrow, Sql, n, i

    Set SomeName = Sheets("SomeName")

    Lastrow = SomeName.Range("B" & Rows.Count).End(xlUp).Row

    For i = 2 To Lastrow

        If i < Lastrow Then
            Sql = Sql + "(" + CStr(SomeName.Range("A" & i).Value) + ", '" + SomeName.Range("B" & i).Value + "', '" + SomeName.Range("C" & i).Value + "', '" + SomeName.Range("D" & i).Value + "', '" + SomeName.Range("E" & i).Value + "', '" + SomeName.Range("F" & i).Value + "'),"
        Else
            Sql = Sql + "(" + CStr(SomeName.Range("A" & i).Value) + ", '" + SomeName.Range("B" & i).Value + "', '" + SomeName.Range("C" & i).Value + "', '" + SomeName.Range("D" & i).Value + "', '" + SomeName.Range("E" & i).Value + "', '" + SomeName.Range("F" & i).Value + "')"
        End If

    Next i

    Set CurConn = New ADODB.Connection
    CurConn.Open "Provider=SQLNCLI11;Server=SERVER\INSTANCE;Database=DATABASENAME;Trusted_Connection=yes;"
    CurConn.CommandTimeout = 180
    CurConn.CursorLocation = adUseClient

    Sql = "MERGE [dbo].[SomeName] as target " _
    & "USING (" _
    & "    SELECT [Userid]" _
    & "          ,[Login]" _
    & "          ,[name]" _
    & "          ,[org]" _
    & "          ,[pos]" _
    & "          ,[mail]" _
    & "    FROM (VALUES" _
    & Sql _
    & "    ) as t([Userid],[Login],[name],[org],[pos],[mail])" _
    & ") as source " _
    & "ON target.[Userid] =source.[Userid] AND target.[Login] =source.[Login] " _
    & "WHEN MATCHED THEN " _
    & "    UPDATE SET  target.[name] =source.[name], " _
    & "                target.[org] =source.[org], " _
    & "                target.[pos] =source.[pos], " _
    & "                target.[mail] = Source.[mail] " _
    & "WHEN NOT MATCHED THEN " _
    & "    INSERT ([Userid],[Login],[name],[org],[pos],[mail]) " _
    & "    VALUES (source.[Userid],source.[Login],source.[name],source.[org],source.[pos],source.[mail]); "

    MsgBox ("Success!")
    CurConn.Execute (Sql)

    CurConn.Close
    Set rst = Nothing
    Set CurConn = Nothing
End Sub

It is weird to convert Excel table to VALUES (1,2,3,4,5,6),(7,8,9,10,11,12)..etc but it works fine. You can adopt this solution and f.e. insert into some temporary table and then merge it like shown above.

RussAwesome · Accepted Answer · 2016-08-25 12:18:49Z

1

Create another table with the same schema called MaterialStaging. Bulk insert into this table instead of your regular table.

Then, run a stored procedure that uses the MERGE command to look for differences between your production table and the staging table, and update or insert the differences.

answered Aug 25, 2016 at 12:18

RussAwesome

4645 silver badges18 bronze badges

Comments

ASH · Accepted Answer · 2016-12-07 12:46:43Z

0

Don't use VB for this. Do it in SQL Server, and use the MERGE command, as Russ suggested.

https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

answered Dec 7, 2016 at 12:46

ASH

20.5k28 gold badges115 silver badges246 bronze badges

Collectives™ on Stack Overflow

Update SQL Table from excel file with vb.net

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related