SQL Bulk Update from Excel

Below is the sample code in C# for bulk upload of data from excel to Sql Server table.

Steps:

1-Write connection string for Sql Database

2-Coonection string for Excel sheet

3-Query to get required Data from excel sheet

4-If we need specific data from excel sheet then we can mention as FROM [Sheet1$A3:o12],So that it will read from A3 upto o12

5-Fille that data into one dataset

6-If need to do any filteration on the excel data we can do on that dataset using dataview rowfilter

7-Using column mapping ,fill the excel data into sql database table

8-Column mapping is required to specify the excel column name to Sql table column name

9-Destination table name should be specified .

 

Imports System.Data.SqlClient

Imports System.Data.OleDb

Imports System.Text.RegularExpressions

Imports System.IO

Imports System.Data

Partial Class _Default

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try

            Dim con As String = "Write your connection string to connect sql database"

            Dim sqlcon As SqlConnection = New SqlConnection(con)

            If Not FileUpload1.HasFile Then

                Return

            End If

            Dim PathName As String = ""

            If FileUpload1.HasFile Then

                Dim filepath As String = FileUpload1.PostedFile.FileName

                Dim pat As String = "\\(?:.+)\\(.+)\.(.+)"

                Dim r As New Regex(pat)

                'run

                Dim m As Match = r.Match(filepath)

                Dim file_ext As String = m.Groups(2).Captures(0).ToString()

                Dim filename As String = m.Groups(1).Captures(0).ToString()

                Dim file As String = filename & "." & file_ext

NOTE 'Below code is to save the Excel file in local folder'

NOTE 'Its not mandatory'

                FileUpload1.PostedFile.SaveAs(Server.MapPath("Uploads\") & file)

 

                PathName = Server.MapPath("Uploads\") & file

            End If

 'Below is the connection string for excel sheet'

            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + PathName + ";Extended Properties=Excel 8.0;")

          

'Below is Query for excel data'

            Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter("Select '0' as BatchID,'0' as BatchDate,* FROM [Sheet1$] ", connection)

            Dim dsk As New DataSet

            oleAdapter.Fill(dsk)

            Dim excelData As New DataTable("ExcelData")

            exceldata = dsk.Tables(0)

            If sqlcon.State = ConnectionState.Closed Then

                sqlcon.Open()

            End If

            Using copy As New SqlBulkCopy(sqlcon)

                

NOTE 'Below is the code for mapping Source or Excel table to Destination or Sql database table'

                'copy.ColumnMappings.Add("BatchID", "BatchID")

                'copy.ColumnMappings.Add("BatchDate", "BatchDate")

                'copy.ColumnMappings.Add("compPartNumber", "compPartNumber")

                'copy.ColumnMappings.Add("PoUnitPrice", "PoUnitPrice")

               

                copy.DestinationTableName = "Give the destination table name"

                copy.WriteToServer(excelData)

            End Using

        Catch ex As Exception

        End Try

    End Sub

End Class