使用前准备:
1.启用Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
2.关闭Ad Hoc Distributed Queries exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',1
reconfigure
--方法1:
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=d:/abc/t.xls', [sheet1$])
--方法2:
需要安装:AccessDatabaseEngine
地址:http://www.microsoft.com/zh-cn/download/confirmation.aspx?id=23734
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=c:/1.xls', [abc$])