data import error in openrowset with variable path

Question

i am importing my data from excel file to Sql Database using openrowset following are my command

declare @path varchar(100)='E:\11.08.2017 .xlsx;'

SELECT * into palwal12 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database='+@path+' HDR=YES; IMEX=1', 'SELECT * FROM [sheet1$]')

i successfully insert data into table while using simple query. but when if i want to use variable path to insert data then i m having an error as incorrect syntax near '+'. i thought my syntax for using path variable is wrong. somebody could help me please.

Jayvee · Accepted Answer · 2017-09-20 09:07:53Z

1

You can't use variables in openrowset parameters, they have to be string literals What you can do is create a dynamic sql and then execute it. example:

Declare @sql varchar(max) = your sql
exec(@sql)

answered Sep 20, 2017 at 9:07

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8205502 Over a year ago

thanx @jayvee with the help of your answer i finally run my query

user8205502 · Accepted Answer · 2017-09-20 09:28:23Z

0

as @jayvee suggested me i created a dynamic Sql and execute my query as following

`DECLARE @sql varchar(MAX);
DECLARE @path varchar(255);
SET @path = 'E:\11.08.2017 .xlsx;'
SET @sql = 'SELECT * INTO ravan FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
  ''Excel 12.0; Database='+@path+' HDR=YES; IMEX=1'', 
  ''SELECT * FROM [sheet1$]'')';

EXEC (@sql)

answered Sep 20, 2017 at 9:28

user8205502

1001 silver badge11 bronze badges

Collectives™ on Stack Overflow

data import error in openrowset with variable path

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related