sql2008_x64 读取excel

 1 --开启导入功能
 2 exec sp_configure ‘show advanced options‘,1
 3 reconfigure
 4 exec sp_configure ‘Ad Hoc Distributed Queries‘,1
 5 reconfigure
 6 --允许在进程中使用ACE.OLEDB.12
 7 EXEC master.dbo.sp_MSset_oledb_prop N‘Microsoft.ACE.OLEDB.12.0‘, N‘AllowInProcess‘, 1
 8 --允许动态参数
 9 EXEC master.dbo.sp_MSset_oledb_prop N‘Microsoft.ACE.OLEDB.12.0‘, N‘DynamicParameters‘, 1
10 
11 --导入临时表 
12  EXEC (‘SELECT * FROM OPENROWSET(‘‘Microsoft.ACE.OLEDB.12.0‘‘, ‘‘Excel 12.0;Database=D:\AGS-1249.xlsx;DHR=YES‘‘, ‘‘SELECT * FROM [Sheet1$]‘‘)‘)
13 --select * from opendatasource(‘Microsoft.Ace.OLEDB.12.0‘,‘Data Source=D:/AGS-1249.xlsx;Extended properties=Excel 12.0;hdr=yes;IMEX=1‘)...[sheet1$]
14 --注意这里，要先关闭外围的设置，然后再关闭高级选项
15 exec sp_configure‘Ad Hoc Distributed Queries‘,0
16 reconfigure
17 exec sp_configure‘show advanced options‘,0
18 reconfigure
19 --关闭ACE.OLEDB.12的选项
20 EXEC master.dbo.sp_MSset_oledb_prop N‘Microsoft.ACE.OLEDB.12.0‘, N‘AllowInProcess‘, 0
21 EXEC master.dbo.sp_MSset_oledb_prop N‘Microsoft.ACE.OLEDB.12.0‘, N‘DynamicParameters‘, 0