Importing data from Excel spreadsheet into SQL Server database using VB.NET

I was just asked to do this at my work and here's the code that did the trick for me.
This uses the new SqlBulkCopy class in ASP.NET 2.0 which lets you easily copy bulk amounts of data from one source to another.

The function that does the SQL bulk import is as follows

Protected Sub SqlBulkCopyImport(ByVal dtExcel As DataTable)
Using conn As New SqlConnection(YOURCONNECTIONSTRING)
' Open the connection. 
conn.Open()

Using bulkCopy As New SqlBulkCopy(conn)
' Specify the destination table name. 

bulkCopy.DestinationTableName = "dbo.tblImportRG"
' System.Data.IDataReader()
bulkCopy.ColumnMappings.Clear()

' For Each dc As DataColumn In dtExcel.Columns
' Because the number of the test Excel columns is not  
' equal to the number of table columns, we need to map  
' columns. 
'Here we manually add the mappings from Excel file (First paratmeter) to SQL table (Second parameter)

bulkCopy.ColumnMappings.Add("id", "NWCarrierID")
bulkCopy.ColumnMappings.Add("acct_master_id", "acct_master_id")
'....And so on

' Next

' Write from the source to the destination. 
bulkCopy.WriteToServer(dtExcel)
End Using
End Using
End sub

This function can be called from another function as follows:


Protected Function RetrieveData(ByVal strConn As String) As DataTable
Dim dtExcel As New DataTable()

Using conn As New OleDbConnection(strConn)
' Initialize an OleDbDataAdapter object.
Dim da As New OleDbDataAdapter("select * from [Sheet1$]", conn)

' Fill the DataTable with data from the Excel spreadsheet.
da.Fill(dtExcel)

End Using
Return dtExcel
End Function



Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
'Try
If importFileUpload.HasFile Then
' Get the name of the Excel spreadsheet to upload.
Dim strFileName As String = Server.HtmlEncode(importFileUpload.FileName)

' Get the extension of the Excel spreadsheet.
_strExtension = Path.GetExtension(strFileName)

' Validate the file extension.
If _strExtension <> ".xls" AndAlso _strExtension <> ".xlsx" Then
'Tell the user to upload an excel file.
Return
End If

' Generate the file name to save.
_strUploadFileName = "~/Library/UploadFiles/" & DateTime.Now.ToString("yyyyMMddHHmmss") & _strExtension

' Save the Excel spreadsheet on server.
importFileUpload.SaveAs(Server.MapPath(_strUploadFileName))

' Generate the connection string for Excel file.
Dim strExcelConn As String = ""

' There is no column name In a Excel spreadsheet.
' So we specify "HDR=YES" in the connection string to use
' the values in the first row as column names.
If _strExtension = ".xls" Then
' Excel 97-2003
strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(_strUploadFileName) & ";Extended Properties='Excel 8.0;HDR=YES;'"
Else
'Excel(2007)
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath(_strUploadFileName) & ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"
End If

Dim dtExcel As DataTable = RetrieveData(strExcelConn)

' Get the row counts before importing.

SqlBulkCopyImport(dtExcel)

' Get the row counts after importing.


' Display the number of imported rows.
lblMessages.Text = Convert.ToString(GetRowCounts()) & " rows were imported into tblImportRG table"
btnSaveTariff.Visible = True

End If

End If


End Sub


Here GetRowCounts() is a simple function that returns the number of rows in the table.