如何动态地从sql中的文件夹动态读取excel [英] how to read dynamiclly excel from folder in sql dynamically

问题描述

朋友们...

我想使用sql动态阅读excel.

Hi Friends...

I want to read excel dynamically using sql.

Declare @Provider nvarchar(100)
Declare @ConnectionString nvarchar(1000)
Declare @FileName  nvarchar(1000)
Declare @query nvarchar(4000)
Declare @collMonth nvarchar(6)

set @Provider= N'Microsoft.ACE.OLEDB.12.0'
set @ConnectionString=N'Excel 12.0 Xml;HDR=YES;Database='
--set @FileName=N'E:\SharedE\chidTest\2011_NS.xlsx'  
--set @FileName=N'E:\SharedE\chidTest\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx'
set @FileName =N'E:\SharedE\chidTest\MisReportsTest\SysAdmin\MISCREPORTS\EXCELUPLOADS\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx'
set @query=N'Select * From [Sheet1$]' 
set @collMonth=N'201104'

--SELECT * into #APColln FROM OPENROWSET(@FileName);
declare @sql  nvarchar(max)
set @ConnectionString = @ConnectionString + @FileName 

SET @sql  = 'SELECT * into #APColln FROM OPENROWSET(' + QUOTENAME(@Provider, '''') + ', ' + QUOTENAME(@ConnectionString, '''') + ', ' + QUOTENAME(@Query, '''') + ')'

print @sql

这是我的查询.

我可以读取前2个文件名.

无法读取第3个.

This is my Query.

I can read first 2 filenames.

Not able read 3 rd one.

推荐答案

' 集合 @ collMonth = N ' 201104' - 选择*到#APColln FROM OPENROWSET(@FileName); 声明 @ sql nvarchar (最大值) 设置 @ ConnectionString = @ ConnectionString + @ FileName SET @ sql = ' 从OPENROWSET(' + QUOTENAME( @ Provider ， ' ''')+ ' ， ' + QUOTENAME( @ ConnectionString ，' ''')+ ' ，' + QUOTENAME(' ' 打印 @ sql

' set @collMonth=N'201104' --SELECT * into #APColln FROM OPENROWSET(@FileName); declare @sql nvarchar(max) set @ConnectionString = @ConnectionString + @FileName SET @sql = 'SELECT * into #APColln FROM OPENROWSET(' + QUOTENAME(@Provider, '''') + ', ' + QUOTENAME(@ConnectionString, '''') + ', ' + QUOTENAME(@Query, '''') + ')' print @sql

这是我的查询.

我可以读取前2个文件名.

无法读取第三个文件.

This is my Query.

I can read first 2 filenames.

Not able read 3 rd one.

由于前两个文件与最后一个文件来自不同的目录，因此请检查子目录的权限:
-MisReportsTest
-SysAdmin
-MISCEPORTS
-EXCELUPLOADS
另外，在运行查询时，请检查文件名是否正确以及文件是否未打开.

通过权限时，请检查运行SQL Server服务的凭据是否具有访问权限.

Since the first two files come from a different directory than the last one, check the permissions on subdirectories:
- MisReportsTest
- SysAdmin
- MISCREPORTS
- EXCELUPLOADS
Also check that the file name is correct and the file isn''t open when you run the query.

When going through the permissions, check that the credentials under which SQL Server service is run, have access.

请尝试以下操作:

Try this:

SET @sql  = 'INSERT INTO #APColln (Field1, Field2, Fieldn...) ' +
        'SELECT * ' +
         'FROM OPENROWSET(' + @Provider + ', ' + @ConnectionString + ', ' + @Query +  ') AS DT'
EXEC(@sql)

这篇关于如何动态地从sql中的文件夹动态读取excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！