Excel to Sql adding certain rows to sql server

excel sql sqlbulkcopy vb.net

Question

cmd.CommandText = (Convert.ToString("SELECT * From [") & excelSheet) & "] Order By" & columnCompany & "OFFSET 0 ROWS  FETCH NEXT 100000 ROWS ONLY "

I am trying to map data form excel to an sql table but the excel table has a lot of records in it, so I run out of memory if I try to add all of the records at once. So what I am trying to do is use this line of code

cmd.CommandText = (Convert.ToString("SELECT * From [") & excelSheet) & "] Order By B OFFSET 0 ROWS  FETCH NEXT 100000 ROWS ONLY " 

So that 100000 row will be added. Then I intend to clear the data table and do the same only from 100001 to 200000 and so on.

But I cant get this to work correctly, it works fine when it is just SELECT TOP 100000 FROM... but then I dont know how to get the next 100000 record. I think the Order By is where the issue is as I'm not sure how to get the correct column.

Here is my code Below

 Private Sub btnMap_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMap.Click

    'Allows columns to be moved mapped in the correct order
    Dim nbColumnsToTransfer As Integer = dgvMapped.Columns.GetColumnCount(1)

    Dim indexes As List(Of Integer) = (From column As DataGridViewColumn In dgvLoadedata.Columns.Cast(Of DataGridViewColumn)() _
                                          Take nbColumnsToTransfer _
                                          Order By column.DisplayIndex _
                                          Select column.Index).ToList()

    Dim columnCompany As Integer = indexes(0)
    Dim columnEmail As Integer = indexes(1)
    Dim columnFirstName As Integer = indexes(2)
    Dim columnSurname As Integer = indexes(3)
    Dim columnPostCode As Integer = indexes(4)
    Dim columnTelephone As Integer = indexes(5)
    Dim columnEmployeeBand As Integer = indexes(6)

    Dim DeleteConnection As New Data.SqlClient.SqlConnection
    DeleteConnection.ConnectionString = "Data Source=DC01\SQLEXPRESS;Initial Catalog=YCM;User ID=sa;Password=S0rtmypc!"
    DeleteConnection.Open()
    Dim sqlDTM As String = "Delete From TempMapped"
    Dim command As New SqlCommand(sqlDTM, DeleteConnection)
    command.ExecuteNonQuery()
    Dim sqlDTSR As String = "Delete From TempSplitReq"
    command = New SqlCommand(sqlDTSR, DeleteConnection)
    command.ExecuteNonQuery()
    DeleteConnection.Close()

    If chkContactSplitReq.Checked = True Then

        Dim dt As New DataTable()
        Using con As New OleDbConnection(conString)
            Using cmd As New OleDbCommand()
                Using oda As New OleDbDataAdapter()
cmd.CommandText = (Convert.ToString("SELECT * From [") & excelSheet) & "] Order By" & columnCompany & "OFFSET 0 ROWS  FETCH NEXT 100000 ROWS ONLY ""
                    cmd.Connection = con
                    con.Open()
                    oda.SelectCommand = cmd
                    oda.Fill(dt)
                    con.Close()
                End Using
            End Using
        End Using

        Dim connection As String = "Data Source=DC01\SQLEXPRESS;Initial Catalog=YCM;User ID=sa;Password=S0rtmypc!"
        Using cn As New SqlConnection(connection)
            cn.Open()
            Using copy As New SqlBulkCopy(cn)
                copy.ColumnMappings.Add(columnCompany, 0)
                copy.ColumnMappings.Add(columnEmail, 1)
                copy.ColumnMappings.Add(columnFirstName, 2)
                copy.ColumnMappings.Add(columnPostCode, 3)
                copy.ColumnMappings.Add(columnTelephone, 4)
                copy.DestinationTableName = "TempSplitReq"
                copy.WriteToServer(dt)
            End Using
        End Using

        Dim SplitConnection As New Data.SqlClient.SqlConnection
        SplitConnection.ConnectionString = "Data Source=DC01\SQLEXPRESS;Initial Catalog=YCM;User ID=sa;Password=S0rtmypc!"
        SplitConnection.Open()
        Dim sqlSplit As String = "TempSplitReq_SaveToMapped"
        Dim commandSplit As New SqlCommand(sqlSplit, SplitConnection)
        commandSplit.ExecuteNonQuery()
        SplitConnection.Close()

    Else

        Dim dt As New DataTable()
        Using con As New OleDbConnection(conString)
            Using cmd As New OleDbCommand()
                Using oda As New OleDbDataAdapter()
                    cmd.CommandText = (Convert.ToString("SELECT * From [") & excelSheet) + "] "
                    cmd.Connection = con
                    con.Open()
                    oda.SelectCommand = cmd
                    oda.Fill(dt)
                    con.Close()
                End Using
            End Using
        End Using

        Dim connection As String = "Data Source=DC01\SQLEXPRESS;Initial Catalog=YCM;User ID=sa;Password=S0rtmypc!"
        Using cn As New SqlConnection(connection)
            cn.Open()
            Using copy As New SqlBulkCopy(cn)
                copy.ColumnMappings.Add(columnCompany, 0)
                copy.ColumnMappings.Add(columnEmail, 1)
                copy.ColumnMappings.Add(columnFirstName, 2)
                copy.ColumnMappings.Add(columnSurname, 3)
                copy.ColumnMappings.Add(columnPostCode, 4)
                copy.ColumnMappings.Add(columnTelephone, 5)
                copy.DestinationTableName = "TempMapped"
                copy.WriteToServer(dt)
            End Using
        End Using

    End If
    MsgBox("Data Mapping Complete")

End Sub

Popular Answer

A SqlBulkCopy object has a BatchSize property. I have a similar situation and set

copy.BatchSize = 1000

And mine works with a large spreadsheet import. Adjust this property in your environment for better performance.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why