Parse Excel file using vb.net

The following method will load excel file in datareader which will be inserted to Sql server via Sqlbulkcopy.


Imports Microsoft.Office.Interop
Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class clsLoadExcel

Function Process(ByVal filePath As String) As Boolean

Dim objExcelConn As _
New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

Dim objQuery As String
Dim objCMD As OleDbCommand
Dim objDR As OleDbDataReader

Dim sqlConn As New SqlConnection
Dim sqlCmd As SqlCommand = New SqlCommand(sqlConn)
Dim sqlBCopy As New SqlBulkCopy(sqlConn)

sqlConn.ConnectionString = "data source=servername;Integrated security=true;Initial Catalog=db;"

objQuery = "SELECT * FROM [Sheet1$]" 'you can change your sheet name

Try
objCMD = New OleDbCommand(objQuery, objExcelConn)
objExcelConn.Open()          
objDR = objCMD.ExecuteReader

' open sql connection
sqlConn.Open()

' now write to sql
sqlBCopy.DestinationTableName = "TempTable"
sqlBCopy.WriteToServer(objDR)
       
Catch ex As Exception

Finally

If Not IsNothing(objDR) Then

objDR.Close()
End If

sqlConn.Close()
sqlBCopy.Close()
End Try

End Function

End Class