Stored procedure to import excel sheet content to

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Stored procedure to import excel sheet content to

Author

Topic

gupnupur
Starting Member

2 Posts

Posted - 2007-10-17 : 05:08:48

Hi,
I have created a stored procedure which imports the contents of the excel sheet into the sql server. I am using the syntax as
"declare cur_Inoperative cursor for Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0; Database=D:\Nupur_GRAPV2.0\Inoperative.xls',Sheet1$) order by 1".
as of now I am hard coding the database path inside the cursor, but i need to pass this path as stored procedure input parameter. Passing the path is working but i am not able to use that passed parameter inside the cursor. can anyone help me in replacing the actual path with the input path variable.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-17 : 05:22:27

You need to use dynamic sql in this case.

Declare @sql nvarchar(3000), @Path Nvarchar(1000)

set @Path = N'D:\Nupur_GRAPV2.0\Inoperative.xls'

set @sql = N'declare cur_Inoperative cursor for Select * from OPENROWSET (''Microsoft.Jet.OleDB.4.0'', ''EXCEL 8.0; Database=' + @Path + ''',Sheet1$)'

Exec(@sql)

Open cur_Inoperative 
-- Do cursor operations
...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

gupnupur
Starting Member

2 Posts

Posted - 2007-10-24 : 02:29:26

Thanks, it's working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources