Excel in OLEDB

[Guest]

Hello,

I am looking to open an Excel spreadsheet in ADO.NET using OLEDB's Jet engine. I have a conn string as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.xls;Extended Properties=Excel 8.0;

I am attempting to put together the SQL to query this spreadsheet. The examples I have found all look like:

Select * From [Sheet1$]

but this explicitly names the worksheet in the spreadsheet. Is there a way to indicate the worksheet by index instead of name? I am attempting to build an automatic parser for an excel spreadsheet and am looking to query the first worksheet but its name could vary from spreadsheet to spreadsheet. How should I do this? If it is not possible to reference the worksheet within the spreadsheet by index in the SQL Select statement, then is there a way to discover the name of the worksheet at run time without starting an instance of Excel? Thanks for your help!

 

[Doug Bell]

You might have to Open the Excel Workbook Object first and retrieve a list
of the sheet names then construct and run your SQL

Doug

Hello,

I am looking to open an Excel spreadsheet in ADO.NET using OLEDB's Jet

engine. I have a conn string as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.xls;Extended Properties=Excel 8.0;

I am attempting to put together the SQL to query this spreadsheet. The

examples I have found all look like:

Select * From [Sheet1$]

but this explicitly names the worksheet in the spreadsheet. Is there a

way to indicate the worksheet by index instead of name? I am attempting to
build an automatic parser for an excel spreadsheet and am looking to query
the first worksheet but its name could vary from spreadsheet to spreadsheet.
How should I do this? If it is not possible to reference the worksheet
within the spreadsheet by index in the SQL Select statement, then is there a
way to discover the name of the worksheet at run time without starting an
instance of Excel? Thanks for your help!

 

[Guest]

I know that is possible, but I am running this on a web server and opening an instance of Excel isn't scalable. Is there another way to get the worksheet name or, better yet, just designate the worksheet by its index?

You might have to Open the Excel Workbook Object first and retrieve a list
of the sheet names then construct and run your SQL

Doug

Hello,

I am looking to open an Excel spreadsheet in ADO.NET using OLEDB's Jet

engine. I have a conn string as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.xls;Extended Properties=Excel 8.0;

I am attempting to put together the SQL to query this spreadsheet. The

examples I have found all look like:

Select * From [Sheet1$]

but this explicitly names the worksheet in the spreadsheet. Is there a

way to indicate the worksheet by index instead of name? I am attempting to
build an automatic parser for an excel spreadsheet and am looking to query
the first worksheet but its name could vary from spreadsheet to spreadsheet.
How should I do this? If it is not possible to reference the worksheet
within the spreadsheet by index in the SQL Select statement, then is there a
way to discover the name of the worksheet at run time without starting an
instance of Excel? Thanks for your help!

 

[Jamie Collins]

You might have to Open the Excel Workbook Object first and retrieve a list
of the sheet names then construct and run your SQL

You can use ADO's OpenSchema to get a list of all Excel tables and
parse their names to get a just the worksheets:

http://groups.google.com/[email protected]

However, I know of no way of using ADO to find which is the first
sheet. The first sheet is not always named Sheet1 and vice versa.

Jamie.

--

 

[Guest]

Thanks Jamie. I looked at the post and it uses ASP. I'll attempt to convert it to ASP.NET. This should do the trick!