SqlBulkCopy doesn't copy all the rows from Excel sheet

sqlbulkcopy sql-server vb.net

Question

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;""")
    Try
        ExcelConnection.Open()
    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
    SQLconn.Open()

    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)

        bulkCopy.DestinationTableName = "ItemDetails"

        Try
            objDR = objCmdSelect.ExecuteReader
            If objDR.HasRows = True Then
                bulkCopy.WriteToServer(objDR)
                MessageBox.Show("You Successfuly import the excel file")
                objDR.Close()
                SQLconn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        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
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