, ,

Dim sSourceConstr As String = [String].Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=’Excel 12.0;'”, Server.MapPath(“Excel”) + “//” + “test.xlsx”)
Dim sDestConstr As String = ConfigurationManager.ConnectionStrings(“TestConnection”).ConnectionString
Dim sSourceConnection As New OleDbConnection(sSourceConstr)
Using sSourceConnection
Dim sql As String = String.Format(“select * from [{0}$]”, “data”)
Dim command As New OleDbCommand(sql, sSourceConnection)
Using dr As OleDbDataReader = command.ExecuteReader()
Using bulkCopy As New SqlBulkCopy(sDestConstr)
bulkCopy.DestinationTableName = “Emp”
‘You can mannualy set the column mapping by the following way.
bulkCopy.ColumnMappings.Add(“Name”, “Name”)
bulkCopy.ColumnMappings.Add(“Class”, “Class”)
bulkCopy.ColumnMappings.Add(“RollNo”, “RollNo”)
End Using
End Using
End Using