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
Solved

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

Posted on 2010-11-11
6
565 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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