Excel to Dataset using OLEDB Connection

There is an easy way to fetch the data from Excel using OLEDB connection. In this article I am going to explain how the fetch the data from the Excel using OLEDB.

You can use this code to retrieve the data from Excel to Dataset

Dim strconnection As String

Dim dadap As New OleDbDataAdapter

Dim ds As New DataSet

Dim excelSheetName As String

'Sheet Name in work book

excelSheetName = "Summary Sheet$"

'Connection string for retrieving the data from the Excel

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath + "; Extended Properties=Excel 8.0;"

dadap = New OleDbDataAdapter("SELECT * FROM [" + excelSheetName +]", strconnection)

'Fill data into the Dataset

dadap.Fill(ds, "sheet1")

datagridview.datasource= ds.Tables(0)

Here I have used OLEDB objects. After Exection this lines of code you will get the data from DataGridview.

Note: This connection string works only for .xls format that means before MS Office 2007. You need change the connection if your going to work for .xlsx format, and the connection string will be

strconnection = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" + strPath + "; Extended Properties =Excel 12.0"

Other than there are some parameter you can add to the connection string . but this parameter are not mandatory 

“HDR=Yes;” - indicates that the first row contains columnnames, not data.

 “HDR=No;” - indicates the opposite. 

“IMEX=1” - is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash. 

If we comporise all parametes we will get

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

Note: when we use HRD and IMEX parameter we need to supply with double quotes “” otherwise it will throw error. And HRD and IMEX is same for .xls and .xlsx

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”