SqlBulkCopy doesn't copy all the rows from Excel sheet

sqlbulkcopy sql-server vb.net


I developed a Windows app to import Excel file and write the data into a SQL Server database table. The application works well but it starts writing from the line 27 or 30 and sometimes from line 29 of the Excel sheet. I need all the rows to be written to the database table from line 1 to line 4500.

My code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim fdlg As OpenFileDialog = New OpenFileDialog
    fdlg.Title = "Open File Dialog"
    fdlg.InitialDirectory = "C:\"
    fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
    fdlg.FilterIndex = 2
    fdlg.RestoreDirectory = True
    If fdlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
        ExcelFileName = fdlg.FileName
    End If

    'Excel 2007
    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFileName + ";Extended Properties=""Excel 12.0 Xml;HDR=No;""")
    Catch ex As Exception
    End Try

    Dim expr As String = "SELECT * FROM [Sheet1$]"

    Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
    Dim objDR As OleDbDataReader

    Dim SQLconn As New SqlConnection()
    Dim ConnString As String = "Data Source=My_PC_Name;Initial Catalog=myDatabase;Integrated Security=True"
    SQLconn.ConnectionString = ConnString

    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)

        bulkCopy.DestinationTableName = "ItemDetails"

            objDR = objCmdSelect.ExecuteReader
            If objDR.HasRows = True Then
                MessageBox.Show("You Successfuly import the excel file")
            End If
        Catch ex As Exception
        End Try
    End Using
End Sub

Popular Answer

SQL is per definition not ordered. So to guarantee the order you will have to add a helper column (say column A) with your row_id. YOu can then use that later to select your data ordered.

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow