OPENROWSET - How to assign a variable in place of the file name to open?

Question

OPENROWSET - How to assign a variable in place of the file name to open?

Navigation

#1 by (0 votes)

1

I make this command and it's true:

SELECT * 
FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=\00.000.00.0000\Arquivos\TI\Arquivos2203408.xlsx;HDR=YES',
    'SELECT * FROM [Rateio$]');

How can I put a variable in place of the '252203408.xlsx' file and that I can use openrowset normally?

NOTE: I want to know this to open several files in a directory.

sql sql-server

asked by anonymous 16.05.2018 / 19:48

1 answer

Html & VB net how to download an img with a link inside a Tag? Redirect before logging in - Angular

score 0 · Answer 1

Use CONCAT ; you would have something like this:

declare @nome_arquivo varchar(255)
select @nome_arquivo = '252203408.xlsx'

SELECT * 
FROM OPENROWSET( 
    'Microsoft.ACE.OLEDB.12.0', 
    CONCAT('Excel 12.0 Xml;Database=\00.000.00.0000\Arquivos\TI\Arquivos\', @nome_arquivo, ';HDR=YES'),
    'SELECT * FROM [Rateio$]');

Edited

I found this answer that seems to fit what you mentioned in the comment. Take a look at how I put the query:

Declare @nome_arquivo varchar(255)
Declare @sql nvarchar(max)

Set @nome_arquivo = '252203408.xlsx'
Set @sql='SELECT * 
FROM OPENROWSET(
               ''Microsoft.ACE.OLEDB.12.0'',
               ''Excel 12.0 Xml;Database=\00.000.00.0000\Arquivos\TI\Arquivos\' + @nome_arquivo + ';HDR=YES;'',
               ''SELECT * FROM [Rateio$]'')'

-- Print @sql
Exec(@sql)