locked
SQL BULK INSERT RRS feed

  • Question

  • User-725520357 posted

    Hi, 

     I am trying to do Bulk Insert from xls file to ms sql database.

     But the problem is,DateTime column is not inserting.It's displaying exception like

    DateTime is not supported.Please help me to solve this problem...

    Here is my code below.

     Protected Sub btnUploadAndImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUploadAndImport.Click
            If Not Page.IsValid Then Exit Sub
    
            'Make sure the demo Excel file was uploaded
            If Path.GetExtension(fupExcel.FileName).ToLower() <> ".xls" Then
                Response.Write("<h1>ERROR: Demo only works with Excel 97-2003 file format...</h1>")
                Exit Sub
            End If
    
            'Save the uploaded Excel spreadsheet to ~/Uploads
            Dim uploadFileName As String = Server.MapPath("~/Uploads/" & Path.GetFileName(fupExcel.FileName))
            fupExcel.SaveAs(uploadFileName)
    
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & uploadFileName & ";" & _
                "Extended Properties=Excel 8.0;"
    
            Dim excelData As New DataTable
    
            Using myConnection As New OleDbConnection(connectionString)
                'Get all data from the InventoryData worksheet
                Dim myCommand As New OleDbCommand
                myCommand.CommandText = "SELECT * FROM [Sheet1$]"
                myCommand.Connection = myConnection
    
                'Read data into DataTable
                Dim myAdapter As New OleDbDataAdapter
                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(excelData)
    
                myConnection.Close()
            End Using
    
    
            'Output starting rowcount
            lblResults.Text = String.Format("STARTING ROW COUNT: {0:N0}<br />", InventoryTableRowCount())
    
            'Start timing!
            Dim sw As Stopwatch = Stopwatch.StartNew()
    
            'Now determine how to insert the data
            Select Case ddlImportChoice.SelectedValue
                Case "0"
                    lblResults.Text &= String.Format("Importing {0} records using {1}...<br />", excelData.Rows.Count, ddlImportChoice.SelectedItem.Text)
                    InsertViaSqlBulkCopyWithoutTransaction(excelData)
    
                Case "1"
                    lblResults.Text &= String.Format("Importing {0} records using {1}...<br />", excelData.Rows.Count, ddlImportChoice.SelectedItem.Text)
                    InsertViaSqlBulkCopyWithInternalTransaction(excelData)
    
                Case "2"
                    lblResults.Text &= String.Format("Importing {0} records using {1}...<br />", excelData.Rows.Count, ddlImportChoice.SelectedItem.Text)
                    InsertViaSqlBulkCopyWithExternalTransaction(excelData)
    
                Case "3"
                    lblResults.Text &= String.Format("Importing {0} records using {1}...<br />", excelData.Rows.Count, ddlImportChoice.SelectedItem.Text)
                    InsertOneAtATimeWithoutTransaction(excelData)
    
                Case Else
                    lblResults.Text &= String.Format("Importing {0} records using {1}...<br />", excelData.Rows.Count, ddlImportChoice.SelectedItem.Text)
                    InsertOneAtATimeWithTransaction(excelData)
            End Select
    
            lblResults.Text &= String.Format("<br />Operation took {0} milliseconds...", sw.ElapsedMilliseconds)
    
            'Output ending rowcount
            lblResults.Text &= String.Format("<br />ENDING ROW COUNT: {0:N0}<br />", InventoryTableRowCount())
    
    
            'Finally, delete the uploaded file
            File.Delete(uploadFileName)
        End Sub
    
    
     Private Sub InsertViaSqlBulkCopyWithoutTransaction(ByVal excelData As DataTable)
            Using destinationConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("InventoryDBConnectionString").ConnectionString)
                destinationConnection.Open()
    
                Using bulkCopy As New SqlBulkCopy(destinationConnection)
                    bulkCopy.DestinationTableName = "Employee"
    
                    'You can optionally specify the batch size... by default, all records are sent to the database in one batch
                    'bulkCopy.BatchSize = 100
    
                    'Define column mappings
                    For Each col As DataColumn In excelData.Columns
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)
                    Next
    
    
                    bulkCopy.WriteToServer(excelData)
                End Using
    
                destinationConnection.Close()
            End Using
        End Sub
    
    
    
    
    Thanks is Advance,
         Prasanth

     

    Sunday, October 23, 2011 6:09 AM

Answers

  • User269602965 posted

    Excel stores dates as a Decimal Number and not what you see FORMATTED as a date in a column.

    (discussed in greater detail in this forum many times before)

    So in your SELECT statement, you have to explicitly CAST the Excel date column to a DATETIME datatype.

    SELECT * will not work because you are giving up the ability to CAST each datatype as required.

    And that will work only if all of your dates are after 01 Jan 1900 (decimal 0 in Excel.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 23, 2011 4:04 PM