One of my recent complete project was to import data from an Excel file into a SQL Server 2005. There are many needs of having done this as part of many different projects out there. I'd like to share with you the method that I used to accomplish the task. Below are some code snippets that I used in my project.
Step 1: Create an Excel Connection
Protected Function ExcelConnection() As OleDbCommand
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("jpweb.xls") & ";" & _ "Extended Properties=Excel 8.0;"
Dim objXConn As New OleDbConnection(xConnStr)
objXConn.Open() Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$] ORDER BY A", objXConn)
Return objCommand
End Function
Step 2: Create a Data Table using the Excel data
Private Function CreateDataTableFromExcel() As DataTable Dim objDataAdapter As New OleDbDataAdapter()
Dim ds As New DataSet()
Dim excelData As New DataTable()
Dim i, ii As Integer
Dim myRow As DataRow
objDataAdapter.SelectCommand = ExcelConnection()
objDataAdapter.Fill(ds, "ExcelData")
Dim dr() As DataRow = ds.Tables("ExcelData").Rows(0).GetChildRows("SQLData")
ds.Tables.Add(New DataTable("Results"))
ds.Tables("Results").Columns.Add("Column0", System.Type.GetType("System.Int32"))
ds.Tables("Results").Columns.Add("Column1", System.Type.GetType("System.Int32"))
myRow = ds.Tables("Results").NewRow
myRow("Column1") = dr(i).Item(0)
ds.Tables("Results").Rows.Add(myRow)
ds.Tables("Results").AcceptChanges()
Return ds.Tables("Results")
End Function
Step 3: Import Data into SQL Server Database
Protected Sub ImportDataIntoSQLServer(ByVal sender As Object, ByVal e As System.EventArgs)
Try
_excelSqlData = CreateDataTableFromExcel()
_db.SqlCmd("Delete * From Table_Name", CommandType.Text)
_db.ExecuteNonQuery()
Using destinationConnection As SqlConnection = _
New SqlConnection(DBAccess.Connection)
destinationConnection.Open()
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = _
"dbo.Table_Name"
Try
bulkCopy.WriteToServer(_excelSqlData)
Catch ex As Exception
Response.Write("
Error: " & ex.Message & "
")
Finally
'Do something here...
End Try
End Using
End Using
Catch ex As Exception
'Do something
End Try
End Sub