SELECT Statement against Excel file?

[ashishraj14]

How can I run sql statement against excel file. I am using office 2003 and want to run retrieve information from a column in excel file.

SELECT DISTINCT Code FROM Excel_File_name? Is this possible?

Thanks

 

[chrissie1]

sort of.

you can use an odbc or oledb link and then run a select against it.

Goto

http://www.connectionstrings.com

for the correct connectionstring to connect to the excell file.



Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ashishraj14]

hi chrissie1

i am getting an error saying "Could not find installable ISAM". What this could be?

thanks

 

[ThatRickGuy]

Woh, I've seen that error before. I can't remember where though, something COM based, back in the VB5 days. Anyways, Google is your friend:

http://support.microsoft.com/kb/209805

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ashishraj14]

ThatRickGuy,

that didn't help, still comes up with the error. this is what i am doing

Dim conn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & "C:\Temp\SSPortfolio.xls" & " '; " & _
"Extended Properties=Excel 8.0;" & "HDR=Yes;" & "IMEX=1")


'! Select the data from Sheet1 of the workbook.
Dim da As New OleDbDataAdapter("SELECT * FROM [SSPortfolio$]", conn)

Dim ds As New DataSet

da.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

does "Extended Properties=Excel 8.0" in the connection refers to interop library 8.0??? i have office 2003 installed, which uses interop library 11.0, cud that be a cause of error???

 

[PankajBanga]

The problem is with Extended Property in Connection string... but you get "installable ISAM" error... it’s weird…

when u google this error u'll get lots of doc, but none of them will say first check connection string

actually Extended Property needs string with double quotes inside the connection string.
Simply do this

Dim strFilePath As String = "C:\Temp\SSPortfolio.xls"

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

 

[RonRepp]

This is what works for me.

Dim Con as ADODB.Connection
Con = New ADODB.Connection
Con.ConnectionString="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Temp\SSPortfolio.xls;DefaultDir=C:\Temp;"


SQLString="SELECT * FROM [SSPortfolio$]" (I'm assuming that SSPortfolio$ is the name of your worksheet.)

I've never tried this with an OLEDBAdapter, however.

I hope this helps.

Ron

Ron Repp