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$]'');'

score 0 · Answer 1 · answered Oct 04 '20 at 10:24

0

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$]'.

answered Oct 04 '20 at 10:24

Ahmmed

1,038
1
5
13

Hi @Runa, I Tried it's not working. Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 0] The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TempExcelSpreadsheet" reported an error. The provider did not give any information about the error. Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41 [Batch Start Line 0] Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TempExcelSpreadsheet". The provider supports the interface, but returns a failure code when it is used. – Varinder Verma Oct 05 '20 at 11:28
can you please guide me on this line ``` declare @linkedServerName sysname = 'TempExcelSpreadsheet' ``` – Varinder Verma Oct 05 '20 at 11:30
I am using SQL Server 2007. Can you please tell me your SQL Server version? It will help me to solve this error. In the mean time , can you please try to give linkserver name small string like [declare @linkedServerName sysname = 'LS'] – Ahmmed Oct 06 '20 at 03:35
You can run this script for configuration "https://drive.google.com/file/d/18laet0VtSwYoAWSHUrxgj7QDRVIB8l64/view?usp=sharing" – Ahmmed Oct 06 '20 at 03:59
SQL Server Management Studio 15.0.18330.0 SQL Server Management Objects (SMO) 16.100.37971.0 Microsoft Analysis Services Client Tools 15.0.19040.0 Microsoft Data Access Components (MDAC) 10.0.17763.1 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 10.0.17763 – Varinder Verma Oct 07 '20 at 05:11

score 0 · Answer 2 · answered Mar 03 '23 at 01:29

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

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

2 Answers2