How to select dynamic sheet Name in Openrow set in SQL query?

Question

I am using the below query to fetch the sales register excel data into a SQL Server table. Usually, the sheet name remains as 'Sales Register' but sometimes it changes to 'Sheet' or Some other names.

Currently, I have mentioned the static sheet name in my query. How I can pick a dynamic name so that even if my sheet name changes I shall be able to fetch the data without error.

Current scenario :

FROM [Sales Register$]'');'

I want it to be like:-

FROM [***ANY NAME*** $]'');'

Code:

SET @filePath1  = 'D:\.......\Sales_Register_'+ @curDate + '.xlsx'

SET @sql2 = 'INSERT INTO [MyDB].[dbo].[SalesRegister] 
                ([Subsidairy],
                 [Date],
                 [Product],
                 [Quantity],
                 [Rate],
                 [Value]) 
             SELECT 
                    [Subsidairy],
                    [Date],
                    [Product],
                    [Quantity],
                    [Rate],
                    [Value]
            FROM OPENROWSET
                (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@filePath1+';
                    HDR=YES; IMEX=1'',
                ''SELECT 
                    [Subsidairy],
                    [Date],
                    [Product],
                    [Quantity],
                    [Rate],
                    [Value]
            FROM [Sales Register$]'');'

Answer 1

You can use below code to get sheet_name from excel document.

DECLARE @tempdata TABLE 
(
   TABLE_CAT varchar(50),
   TABLE_SCHEMA varchar(50),
   TABLE_NAME varchar(50),
   TABLE_TYPE varchar(50),
   TABLE_REMARKS varchar(50)
);
declare @sheet_name nvarchar(100);
declare @linkedServerName sysname = 'TempExcelSpreadsheet'
declare @excelFileUrl nvarchar(1000) = 'D:\opt\Test\Test.xlsx'

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

exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = @excelFileUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

declare @suser_sname nvarchar(256) = suser_sname()

exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null


INSERT INTO @tempdata
exec sp_tables_ex @linkedServerName;

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

After that you can write your code and use variable '@sheet_name' instead of '[Sales Register$]'.

Answer 2

I needed to find all of the names of Excel tabs from a list of Excel files in a directory rather than just one worksheet.

I used value statements to insert my filenames into a temp table. The filename in this table had to have the full directory path included.

I used the answers in this thread to programmatically create that linked server and combined it with this cursor example answer to create my code:

https://stackoverflow.com/a/5856192

This was the code I came up with:

declare @fnTemp Table (filename varchar(200));
insert into @fnTemp
select * from 

(values
('2015 FileName1 Survey.xlsx'), 
('2016 Filename2 Survey.xlsx'), 
('2017 Filename3 Survey.xlsx'), 
('2018 FileName4 Survey.xlsx'), 
('2019 Special Survey.xlsx'),
('Sample1.xlsx')) FN([FileName] )
;
--Select * from @fnTemp;

DECLARE @tempdata TABLE 
(
   TABLE_CAT varchar(100),
   TABLE_SCHEMA varchar(50),
   TABLE_NAME varchar(50),
   TABLE_TYPE varchar(50),
  TABLE_REMARKS varchar(50)
);

declare @sheet_name nvarchar(100);
declare @linkedServerName sysname = 'TempExcelSpreadsheet';
declare @sourcepath nvarchar(100)='C:\DroppedFiles\';
declare @fullUrl nvarchar(2000);


declare files Cursor Read_Only for select [FileName] from @fnTemp

declare @excelFileUrl nvarchar(1000)
declare @suser_sname nvarchar(256) = suser_sname()

open files
fetch next from files into @excelFileUrl
while (@@FETCH_STATUS = 0)
begin

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

Set @fullUrl=@sourcepath + @excelFileUrl

exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 16.0',
    @provider = 'Microsoft.ACE.OLEDB.16.0',
    @datasrc = @fullUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null


INSERT INTO @tempdata
exec sp_tables_ex @linkedServerName;

Update @tempdata set TABLE_CAT = @excelFileUrl where TABLE_CAT is null;
Delete @tempdata where TABLE_NAME like '%Print_titles'

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

Fetch next from files into @excelFileUrl

end
close files
deallocate files

select Table_Name, Table_Cat from @tempdata