[sql server] 整理 openrowset 与 opendatasource 函数连接 Excel 的用法及问题

整理 openrowset 与 opendatasource 函数连接 Excel 的用法及问题

首先做了这样一excel有两个表，如图

-- 一 、openrowset

-- 查询两种方式
-- 1、
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$]'
)
-- 2 、
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
[sheet1$]
)
/*
a1 a2 a3
---------------------- ------- -------------
1 r 10
2 r 11
3 r 12
4 r 13
5 r 14
1 12 15
1 18 16
1 14 17
2 19 NULL------------->因为这里前面的数据是数字
2 30 NULL------------->所以这些非数字就变null了
2 21 NULL------------->这里只有把前面数字变为非数字才行
1 12 NULL
1 18 NULL
1 14 NULL
2 19 NULL
2 30 NULL
2 21 NULL

(17 行受影响)

*/

-- 插入
insert openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$]'
) select 101,'aaa',123
/*--- 这样不行，update delete也不行
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'insert into [sheet1$] values( 12,111,101)'
)
*/

select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$]'
) where a1=101

/*--这样也行
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$] where a1=101'
)
*/
/*
a1 a2 a3
---------------------- -------- ----------------------
101 aaa 123

(1 行受影响)

*/

-- 更新
update openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$]'
) set a2='bbb',a3=345 where a1= 101

select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet1$]'
) where a1=101
/*
a1 a2 a3
---------------------- -------- ----------------------
101 bbb 345

(1 行受影响)

*/

-- 删除
delete from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;HDR=yes;IMEX=2',
'select * from [sheet1$]'
) where a1=101
/*
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "该 ISAM 不支持在链接表中删除数据。"。
消息 7345，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 无法从表 "select * from [sheet1$]" 删除数据。出现可恢复的、特定于提供程序的错误，如 RPC 失败。
*/
delete openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;HDR=yes;IMEX=2',
[sheet1$]
) where a1='101'
/*
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "FROM 子句语法错误。"。
消息 7321，级别 16，状态 2，第 1 行
准备对链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 执行查询"DELETE FROM sheet1$ WHERE `a1`=(1.010000000000000e+002)"时出错。
*/
delete openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;HDR=yes;IMEX=2',
[sheet1$]
) where a2='bbb'
/*
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "该 ISAM 不支持在链接表中删除数据。"。
消息 7345，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 无法从表 "sheet1$" 删除数据。出现可恢复的、特定于提供程序的错误，如 RPC 失败。
*/

--->删除不支持

-- 二 、OPENDATASOURCE 与openrowset基本相同，只有一些地方有差异，后面会讲到

-- 1 查询
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/test.xls";Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'-- " 可带可不带，多个属性必须带
)...[sheet1$]

-- 2 增加
insert OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'
)...[sheet1$]
select '102','ccc','202'

-- 3 更新

update OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'
)...[sheet1$]
set a2='ddd',a3='203' where a1=102
/*
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "UPDATE 语句的语法错误。"。
消息 7321，级别 16，状态 2，第 1 行
准备对链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 执行查询"UPDATE sheet1$ set `a2` = 'ddd',`a3` = (2.030000000000000e+002) WHERE `a1`=(1.020000000000000e+002)"时出错。
*/
-----^^^^^^^^ 似乎对数字类型支持不好

update OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'
)...[sheet1$]
set a2='ddd',a3='203' where a2='ccc'
--这句没问题

-- 4 删除
delete OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'
)...[sheet1$] where a2='bbb'
/*
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "该 ISAM 不支持在链接表中删除数据。"。
消息 7345，级别 16，状态 1，第 2 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 无法从表 "sheet1$" 删除数据。出现可恢复的、特定于提供程序的错误，如 RPC 失败。
*/

一个区别：
对于 Excel 里 Sheet-2 这个表名中含有 ‘ - ’字符，在OPENDATASOURCE中无论如何都无法支持,而openrowset则可解决这个问题

select * from OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"'
)...[sheet-2$]
/*
消息 7314，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 不包含表 "sheet-2$"。该表不存在，或者当前用户没有访问该表的权限。

*/
--- 这个可行
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
'select * from [sheet-2$]'
)

-- 这个不行
select * from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:/test.xls;User ID=;Password=;',
[sheet-2$]
)

------------

特别注意

　　Extended Properties='Excel 8.0;HDR=yes;IMEX=1'

　　A： HDR ( HeaDer Row )设置

　　若指定值为Yes，代表 Excel 档中的工作表第一行是栏位名称

　　若指定值為 No，代表 Excel 档中的工作表第一行就是資料了，沒有栏位名称

　　B：IMEX ( IMport EXport mode )设置

　　IMEX 有三种模式，各自引起的读写行为也不同，容後再述：

　　0 is Export mode

　　1 is Import mode

　　2 is Linked mode (full update capabilities)

　　我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：

　　当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。

　　当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。

　　当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。