Importing Data from EXCEL

• naveenreddy.84

  Ten Centuries

  Points: 1252

  More actions

  June 3, 2009 at 11:21 am

  #210231

  Hi,

  Openrowset is one of the way to import data from excel into the sql server 2005: Below is the query usually used for this

  insert into MyTable

  select from OpenRowset(

  'Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=c:\Report.xls',

  'select * from ReportData'

  )

  However, i need to send the filename to OPENROWSET via a variable. This variable will be an input parameter to the stored procedure having the above statement.

  CREATE PROCEDURE MyProc

  ( Declare @FileName nvarchar(50)

  )

  AS

  BEGIN

  insert into MyTable

  select from OpenRowset(

  'Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=@FileName',

  'select * from ReportData'

  )

  Can it be done? I tried doing this but couldnt achieve this!!

  Kindly advice

  Regards,

  Naveen

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  June 3, 2009 at 12:19 pm

  #1004218

  From Bools On Line (BOL):

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f47eda43-33aa-454d-840a-bb15a031ca17.htm

  OPENROWSET does not accept variables for its arguments.

  If you wish to use OPENROWSET you will have to construct a dynamic T-SQL statement and then Execute that statement.

  Something similiar to:

  ALTER PROCEDURE MyProc

  @FileName nvarchar(50)

  AS

  DECLARE @TSQL AS nvarchar(200)

  SET @TSQL = 'insert into MyTable

  select from OpenRowset(

  ''Microsoft.Jet.OLEDB.4.0'',

  ''Excel 8.0;Database=' + LTRIM(RTRIM(@FileName))

  +', select * from ReportData'

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]