Get Excel sheet name In SQL Server

How to get the sheet names in an Excel file using SQL Sever 2005?

Please be noted that:

• There is no front-end (C#, VB, PHP, etc.);
• I am trying to get the sheet names using nothing but SQL Server 2005 alon开发者_如何学编程e.

Thanks.

There are at least two possibilities for doing this. I will admit up front that I don't have an easy way to check this in SQL Server 2005, only 2008 right now.

1: Create a linked server and use sp_tables_ex and/or sp_columns_ex:

-- Get table (worksheet) or column (field) listings from an excel spreadsheet

-- SET THESE!
declare @linkedServerName sysname = 'TempExcelSpreadsheet'
declare @excelFileUrl nvarchar(1000) = 'c:\MySpreadsheet.xls'
-- /SET

-- Remove existing linked server (if necessary)
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

-- Add the linked server
-- ACE 12.0 seems to work for both xsl and xslx, though some might prefer the older JET provider
exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = @excelFileUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

-- Grab the current user to use as a remote login
declare @suser_sname nvarchar(256) = suser_sname()

-- Add the current user as a login
exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null

-- Return the table/column info
exec sp_tables_ex @linkedServerName
exec sp_columns_ex @linkedServerName

-- Remove temp linked server
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

I found the inspiration for this here.

2: Use Ole Automation Procedures as outlined here. I have not tried this one myself.

You can't. There are two routes to reading data from Excel. One is the COM/OLE automation route which would allow you to enumerate through worksheets in a workbook. That requires a procedural language which TSQL won't do. I don't even think if you allowed CLR methods into the mix, you'd be able to access the Office libraries as they aren't on the BCL list.

The second route would be to use the Jet driver via openquery in this case but as part of the setup, you need to explicitly define the file and worksheet to access. You can forgo listing the worksheet name but even then, Excel does not expose metadata about worksheets to the best of my divination.

I'll delete this answer if someone knows another way but having sliced this problem in a number of ways, I haven't come up with an answer that doesn't boil down to one of the two aforementioned approaches.

Just fleshing out Option 2 - Ole Automation Procedures from Tim's answer, as the link for that is now dead. You can do this with code like the following:

declare @FilePath varchar(max) = '[Excel File].xlsx'
declare @ConnectionString varchar(max) = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+@FilePath+';Extended Properties=''Excel 12.0;HDR=Yes'''
declare @r int, @objConnection int, @objRecordSet int
          exec @r = sp_OACreate 'ADODB.Connection', @objConnection output
if @r = 0 exec @r = sp_OAMethod @objConnection, 'Open', null, @connectionstring
if @r = 0 exec @r = sp_OAMethod @objConnection, 'OpenSchema', @objRecordSet output, 20
if @r = 0 exec @r = sp_OAGetProperty @objRecordSet, 'GetRows'