When I started on this task, I thought it would a very simple thing to do. I mean its a similar concept for both; rows and columns. Well I spent two days banging my head on the desk before coming up with a solution.

Class:

Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class clsImportExcel
'BuildSQLConnString '--Builds a valid SQL connection string
Private Function BuildSQLConnString() As SqlConnectionStringBuilder
Dim connBuild As New SqlConnectionStringBuilder
connBuild.DataSource = "DBServer"
connBuild.InitialCatalog = "DBName"
connBuild.ConnectTimeout = 300
'false = sql auth; true = windows auth
connBuild.IntegratedSecurity = False
'these get turned off with integrated security
connBuild.UserID = "USER"
connBuild.Password = "PASSWORD"
connBuild.PersistSecurityInfo = True
Return connBuild End Function

'BuildXLConnString '--Builds a valid Excel connection string
Private Function BuildXLConnString(ByVal DSource As String) As OleDbConnectionStringBuilder
Dim connBuild As New OleDbConnectionStringBuilder
connBuild.Provider = "Microsoft.Jet.OLEDB.4.0"
connBuild.DataSource = DSource
connBuild.Add("Extended Properties", "Excel 8.0;IMEX=1;HDR=Yes;") 'IMEX=1; - Tells the driver to read everything as intermixed text 'HDR=Yes; - Tells the driver that there is a header row in the sheet
Return connBuild
End Function

'ImportExcelBulk '--Imports an Excel spreadsheet using SQLBulkCopy
Public Function ImportExcelBulk(ByVal SQLTableName As String, ByVal ExcelFile As String, ByVal SheetName As String) As Boolean
'Create the connection strings
Dim excelConnString As String = BuildXLConnString(ExcelFile).ToString
Dim sqlConnString As String = BuildSQLConnString.ToString
'Set up the connection
Using excelConn As New OleDbConnection(excelConnString)
'Excel command object
Dim excelCmd As New OleDbCommand("SELECT * FROM [" + SheetName + "$]", excelConn)
'Open the Excel connection excelConn.Open()
'Set up the DataReader
Dim excelRead As OleDbDataReader = excelCmd.ExecuteReader
'Set up the SQLBulkCopy Using bulkCopy As New SqlBulkCopy(sqlConnString)
'Set the destinationtable
bulkCopy.DestinationTableName = SQLTableName
Try
'Write the data to the server
bulkCopy.WriteToServer(excelRead)
'Success
Return True
Catch ex As Exception
'Error
Return False
Finally
'Close the DataReader
If Not excelRead.IsClosed Then
excelRead.Close()
End If
End Try
End Using 'end bulkCopy
End Using 'end excelConn
End Function
End Class

Usage:

Dim xlImport As New clsImportExcel
If xlImport.ImportExcelBulk("tblNAME", "C:\ExcelFile.xls", "Sheet1") Then
'Worked
MessageBox.Show("Success")
Else
'Failed
MessageBox.Show("Failed")
End If
'End xlImport.ImportExcelBulk

Note the use of ConnectionStringBuilders. I love these since they help eliminate the typos I always seem to have when typing them out.

And there you have it. Importing an Excel spreadsheet into SQL using the SQLBulkCopy feature of VB.Net. Hopefully this save someone else some trouble.