将excel的动态文件路径传递给"OPENROWSET"； [英] pass dynamic file path of excel to "OPENROWSET"

问题描述

我想将excel的动态URL传递给"OPENROWSET".

I want pass dynamic URL of excel to "OPENROWSET".

注意-我正在将excel文件的返回结果传递给游标. 我想将文件路径传递给"@excelpath"， 我尝试了很多方法，但是给出了语法错误.

NOTE - I am passing returned result of excel file to cursor. I want to pass file path to "@excelpath", I have tried many ways but its giving syntax error.

 ALTER procedure [dbo].[import_excel]
(
    @excelpath as nvarchar(max)
)
as      
begin
  set nocount on 
  DECLARE insert_cursor CURSOR FOR 
  select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\memberdata.xlsx', [Sheet1$])
  OPEN insert_cursor;
  FETCH NEXT FROM insert_cursor
      INTO @id_number, @memberName

  WHILE @@FETCH_STATUS = 0
  BEGIN
  -- body of cursor
      FETCH NEXT FROM insert_cursor
      INTO @id_number, @memberName
  END
  CLOSE insert_cursor;
  DEALLOCATE insert_cursor;
END

推荐答案

您必须使用动态SQL构建查询，如

You have to build your query using dynamic SQL, as shown in this question. It would probably be simplest for you to insert the data from your query into a permanent table, then run the cursor over the permanent table. In that way you minimize the amount of SQL you need to work with dynamically.

这篇关于将excel的动态文件路径传递给"OPENROWSET"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！