Retrieve data from an Excel spreadsheet using ADO.NET connection objects

[funcSter]

I want to retrieve data from an Excel file like how I would with a
database. I understand that I would have to use OLE DB. Somehow I
think I cannot get the connection string right, as the bit of code
fails at "objConn.Open()" with the error:
System.Data.OleDb.OleDbException: Could not find installable ISAM.

Can somebody please help me out? I'm still a learning programmer, so
if someone can point out my mistakes, it'll be great! Thanks!

Dim objConn As OleDbConnection
Dim objTrans As OleDbTransaction
Dim objCmd As OleDbCommand
Dim objRead As OleDbDataReader

Dim strSource As String = "C:\temp\data.xls"
Dim strConn As String = "Persist Security Info=False;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + strSource + ";" & _
"Extended Properties=Microsoft Excel 97-2000;"

objConn = New OleDb.OleDbConnection(strConn)
objConn.Open()

Dim strSQLQuery As String = "SELECT * FROM [Sheet1$]"

objCmd.CommandText = strSQLQuery
objRead = objCmd.ExecuteReader()

While objRead.Read()
' Manipulate data
End While

objTrans.Commit()
objConn.Close()
objConn = Nothing

 

[Steven Stein [MSFT]]

It might be the:
Extended Properties=Microsoft Excel 97-2000

You might want to try:
Extended Properties=Excel 8.0


The following topic may help:

Reading Excel Data into a Dataset (Visual Basic)
http://msdn.microsoft.com/library/en-us/dv_vbcode/html/vbtskcodeexamplereadi
ngexceldataintodataset.asp



hope that helps

Steve Stein
VB Team

This posting is provided "AS IS" with no warranties and confers no rights.

--------------------
| From: (e-mail address removed) (funcSter)
| Newsgroups: microsoft.public.dotnet.languages.vb
| Subject: Retrieve data from an Excel spreadsheet using ADO.NET connection
objects
| Date: 19 Jan 2005 10:54:45 -0800
| Organization: http://groups.google.com
| Lines: 35
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 202.91.205.118
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1106160886 9238 127.0.0.1 (19 Jan 2005
18:54:46 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 19 Jan 2005 18:54:46 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.glorb.com!postnews.google.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.dotnet.languages.vb:254806
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| I want to retrieve data from an Excel file like how I would with a
| database. I understand that I would have to use OLE DB. Somehow I
| think I cannot get the connection string right, as the bit of code
| fails at "objConn.Open()" with the error:
| System.Data.OleDb.OleDbException: Could not find installable ISAM.
|
| Can somebody please help me out? I'm still a learning programmer, so
| if someone can point out my mistakes, it'll be great! Thanks!
|
| Dim objConn As OleDbConnection
| Dim objTrans As OleDbTransaction
| Dim objCmd As OleDbCommand
| Dim objRead As OleDbDataReader
|
| Dim strSource As String = "C:\temp\data.xls"
| Dim strConn As String = "Persist Security Info=False;" & _
| "Provider=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" + strSource + ";" & _
| "Extended Properties=Microsoft Excel 97-2000;"
|
| objConn = New OleDb.OleDbConnection(strConn)
| objConn.Open()
|
| Dim strSQLQuery As String = "SELECT * FROM [Sheet1$]"
|
| objCmd.CommandText = strSQLQuery
| objRead = objCmd.ExecuteReader()
|
| While objRead.Read()
| ' Manipulate data
| End While
|
| objTrans.Commit()
| objConn.Close()
| objConn = Nothing
|