SQL链接服务器,访问Excel单元格范围 [英] SQL Linked Server, Access Excel Cell Range
问题描述
 
首先,我有大约一天的sql经验
以下工作原理和:
EXEC sp_dropserver
@server = N'ExcelDataSource'
EXEC sp_addlinkedserver
@server ='ExcelDataSource',
@ srvproduct ='Excel',
@ provider ='Microsoft.ACE.OLEDB.16.0',
@datasrc ='D:\transfer_sql \ test.xlsm',
@ provstr ='Excel 12.0 Macro; HDR = NO;';
$
从ExcelDataSource中选择* ... [data $ ];
现在我希望访问Excel工作表中的特定范围:
从ExcelDataSource中选择* ... [数据$ A8:C12];
但是失败了:
Msg 7314,Level 16,State 1,Line 14
OLE DB提供程序"Microsoft.ACE.OLEDB.16.0"对于链接服务器"ExcelDataSource"不包含表格"data $ A8:C12"。该表不存在或当前用户对该表没有权限。
什么是我错过了吗?
感谢您花时间阅读这篇文章。
最好的问候
"select * from"为您提供可以使用sql访问的可访问列名:
从[SheetName]中选择[Col1],[Col2],...,[ColN]
Hi, first of, I have about a days worth experience with sql
The following works and:
EXEC sp_dropserver
@server = N'ExcelDataSource'
EXEC sp_addlinkedserver
@server = 'ExcelDataSource',
@srvproduct='Excel',
@provider='Microsoft.ACE.OLEDB.16.0',
@datasrc = 'D:\transfer_sql\test.xlsm',
@provstr= 'Excel 12.0 Macro;HDR=NO;' ;
select * from ExcelDataSource...[data$];
Now I wish to access specific ranges within the excel sheet:
select * from ExcelDataSource...[data$A8:C12];
This fails, however:
Msg 7314, Level 16, State 1, Line 14
The OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "ExcelDataSource" does not contain the table "data$A8:C12". The table either does not exist or the current user does not have permissions on that table.
What am I missing?
Thank you for taking the time, reading this far.
Best Regards
The "select * from " gives you the accessible columnnames that you can access with sql:
select [Col1], [Col2], ... , [ColN] from [SheetName]
这篇关于SQL链接服务器,访问Excel单元格范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!