2011-12-12

Read excel to dataset

This sample show how to read data from excel file by using Microsoft.Jet.OLEDB.

More information about excel connectionstring see http://www.connectionstrings.com/excel

Let's sample code.

Private Function ReadExcelFile(ByVal filePath As String, ByVal fileName As String) As DataSet
'' Excel 2003
Dim sConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filePath + fileName)
sConn += ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Dim sSql As String = "SELECT * FROM [Sheet1$]"
'Select excel range command
' Dim sSql As String = "SELECT * FROM [Sheet1$A1:D3]"

Dim pFile As New FileInfo(fileName)
If (pFile.Extension.ToLower() = ".xlsx") Then
' Excel 2007
sConn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", filePath + fileName)
sConn += ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
End If


Dim ds As New DataSet
Dim pConn As New OleDbConnection(sConn)
Try
pConn.Open()
Dim pCmd As New OleDbCommand()
pCmd.Connection = pConn
pCmd.CommandText = sSql

Dim pAdp As New OleDbDataAdapter()
pAdp.SelectCommand = pCmd

pAdp.Fill(ds)
Catch ex As Exception
Return Nothing
Finally
pConn.Close()
End Try

Return ds
End Function

Note
If found error Could not find installable ISAM that mean your connectionstring was wrong.