Jet 引擎可访问Office 97-2003,但不能访问Office 2007。而ACE 引擎既可访问Office 2007,也可访问Office 97-2003。
(Microsoft.ACE.OLEDB.12.0 可访问正在打开的Excel 文件,而Microsoft.Jet.OLEDB.4.0 是不可以的)
--1.Excel导入到sql server
select * into sqz from
OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls','select*from [data$]')
--2.Excel导入到sql server
select * into sqz from
OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls', [data$])
--3.Excel导入到sql server
select * into sqz from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls')...[data$]
--4.Excel导入到sql server
select * into sqz from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=D:\data.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[data$]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--ACE引擎连接 Excel 97-2003
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls','select * from [data$]')
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls', [data$])
select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls')...[data$]
select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:\data.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[data$]
--ACE引擎连接 Excel 2007
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx', 'select * from [data$]')
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx', [data$])
select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx')...[data$]
select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:\data.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[data$]