Dear Sirs
I have used your code to export the excel in to Sql server table.
Here i surprised it importing the excel file in to 2 times in to the sql table.
Kindly help me.
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
'Imports ClosedXML.Excel
Imports System.Net
Imports System.Net.Mail
Partial Class WTextodb
Inherits System.Web.UI.Page
Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
'Upload and save the file
Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)
Dim connString As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
Case ".xls"
'Excel 97-03
connString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 or higher
connString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
Exit Select
End Select
connString = String.Format(connString, excelPath)
Using excel_con As New OleDbConnection(connString)
excel_con.Open()
Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
Dim dtExcelData As New DataTable()
dtExcelData.Columns.AddRange(New DataColumn(11) {New DataColumn("IType", GetType(String)), _
New DataColumn("IDesc", GetType(String)), _
New DataColumn("IDLc", GetType(Date)), _
New DataColumn("IDNc", GetType(Date)), _
New DataColumn("ICFrequency", GetType(String)), _
New DataColumn("ICDays", GetType(Decimal)), _
New DataColumn("IDept", GetType(String)), _
New DataColumn("itag", GetType(String)), _
New DataColumn("itagn", GetType(String)), _
New DataColumn("itagc", GetType(String)), _
New DataColumn("ICGroup", GetType(String)), _
New DataColumn("ICAction", GetType(String))})
' Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1 & "A1:A60") + "]", excel_con)
Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
oda.Fill(dtExcelData)
End Using
excel_con.Close()
Dim conString As String = ConfigurationManager.ConnectionStrings("SQLDbConnection").ConnectionString
Using con As New SqlConnection(conString)
con.Open()
Using sqlBulkCopy As New SqlBulkCopy(con)
'Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.icimaster"
' '[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("IType", "IType")
sqlBulkCopy.ColumnMappings.Add("IDesc", "IDesc")
sqlBulkCopy.ColumnMappings.Add("IDLc", "IDLc")
sqlBulkCopy.ColumnMappings.Add("IDNc", "IDNc")
sqlBulkCopy.ColumnMappings.Add("ICFrequency", "ICFrequency")
sqlBulkCopy.ColumnMappings.Add("ICDays", "ICDays")
sqlBulkCopy.ColumnMappings.Add("IDept", "IDept")
sqlBulkCopy.ColumnMappings.Add("itag", "itag")
sqlBulkCopy.ColumnMappings.Add("itagn", "itagn")
sqlBulkCopy.ColumnMappings.Add("itagc", "itagc")
sqlBulkCopy.ColumnMappings.Add("ICGroup", "ICGroup")
sqlBulkCopy.ColumnMappings.Add("ICAction", "ICAction")
sqlBulkCopy.WriteToServer(dtExcelData)
con.Close()
con.Dispose()
End Using
End Using
End Using
End Sub
End Class
