Get Excel sheet name In SQL Server

Question

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 alone.

Thanks.

Answer 1

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.