Excel data to SQL Server table-CodePudding

I have an Excel file which updates daily. I need to move the data from Excel into a SQL Server table.

I can do it from data import in SQL Server. But the question here is the same Excel files updates daily. Sometimes the data might be wrongly inserted under the columns.

How can I validate the data before importing? How I can import without changing the datatypes? How I can automate this process?

Thanks in advance

CodePudding user response：

Easy!!

' Add reference to Microsoft Active X Data Objects 2.8 Library                                                           

Sub testexportsql()
    Dim Cn As ADODB.Connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.Recordset
    Dim RowCounter As Long

    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long

    Dim ColCounter As Integer


    Set rs = New ADODB.Recordset


    ServerName = "server_name" ' Enter your server name here
    DatabaseName = "db_name" ' Enter your  database name here
    TableName = "table_name" ' Enter your Table name here
    UserID = "" ' Enter your user ID here
     ' (Leave ID and Password blank if using windows Authentification")
    Password = "" ' Enter your password here
    NoOfFields = 10 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading  records
    EndRow = 100 ' Enter row of last record in sheet

     '  CHANGES
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("sheet_name")
     '********

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
    ";Uid=" & UserID & ";Pwd=" & Password & ";"

    rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic

     'EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 1 To NoOfFields
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
        Next ColCounter
        Debug.Print RowCounter
    Next RowCounter
    rs.UpdateBatch

     ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing

End Sub

CodePudding user response：

One of the many solutions would be inserting your excel into a table, cleansing and validating the data first, if successful just copy the middle table into your main table.