CODEWITHSUNDEEP
sql-servert-sqlexcel-2010
Chris Turner
Chris Turner

Reputation: 119

.xlsx to SQL (Database String causing issues)

I have been working on creating a stored procedure to import an .xlsx document into a SQL table. IF I use a static database path for the .xlsx file it will work without any issues. The problem I am running into is that multiple branches of the company will be creating files that are specific to their branch. The file names will always be in the format of Airport Code_date.xlxs.

Can anyone assist me in getting the @pathToFile workign correctly. Everytime I have attempted to add it to the Database string I have received errors. I have not posted full code to make this easier to read. If you need full code please let me know.

Thanks,

FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=' + @pathToFile,
'SELECT * FROM [sheet1$]'
)

Upvotes: 0

Views: 171

Answers (1)

Anon
Anon

Reputation: 10908

You'll need dynamic SQL for this, because the arguments for OPENROWSET have to be constants.

DECLARE @sql varchar(max)

SET @sql = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + QUOTENAME('Excel 12.0;Database='+@pathtofile,CHAR(39)) + ',''SELECT * FROM [sheet1$]'')'

INSERT DestinationTable
EXEC sp_executesql @sql

The QUOTENAME() is to defend against SQL injection, since @pathtofile can't be parameterized.

Upvotes: 4

Related Questions