?
Solved

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

Posted on 2010-11-11
6
Medium Priority
?
600 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
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, CCNA
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 37

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

589 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