[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 檔案可同時支援“讀取”與“寫入”用途。