Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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

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
R_O_B
Asked:
R_O_B
1 Solution
 
chinawalCommented:
>>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
 
Brad Sims, CCNANetwork AdministratorCommented:
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
 
R_O_BAuthor Commented:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NorieCommented:
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
 
R_O_BAuthor Commented:
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
 
R_O_BAuthor Commented:
...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now