Solved

SQL Server, query Excel workbook sheet count and refer by index.

Posted on 2010-11-11
6
569 Views
Last Modified: 2012-05-10
From SQL Server, I'm querying Excel sheets without problem by using:
SELECT * FROM [Sheet1$]

However, in the event I don't know the sheet name, or how many there are, is there a way to query the sheet count and then refer to them by their index?

Thanks.
0
Comment
Question by:R_O_B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:chinawal
ID: 34117436
>>From SQL Server, I'm querying Excel sheets without problem by using:
>>SELECT * FROM [Sheet1$]

How is this possible?
It seems, you exported a particular excel worksheet from a particular excel workbook into SQL Server database.
Or are you using "Linked Servers" like this http://support.microsoft.com/kb/306397?


0
 
LVL 7

Expert Comment

by:Brad Sims
ID: 34117445
I was wondering the same thing.  You can refer to worksheets by index in VBA, but it would just depend on how you are connecting from SQL to Excel.
0
 

Author Comment

by:R_O_B
ID: 34117458
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Norie
ID: 34120824
As far as I know you can't refer to the sheets by index in a query like that

Why would you want/need to anyway?

Even if you could you might end up importing the wrong worksheet - even in Excel the sheet index isn't used that often.

The sheet count might not help either - why not try the linked server?
0
 

Accepted Solution

by:
R_O_B earned 0 total points
ID: 34121263
I'll never import the wrong sheet. I want a raw import of every sheet. I'm moving on... right environment, wrong tool. Thanks all.
0
 

Author Closing Comment

by:R_O_B
ID: 34153305
...
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question