使用 OpenRowSet 和 OpenDataSource 通路 Excel 97-2007
測試檔案：D:/97-2003.xls和D:/2007.xlsx，兩個檔案的内容是一模一樣的。
測試環境：SQL Server 2000 / 2005。
- 接口類型
- 文法一覽
- 系統資料庫設定
- 單一資料類型列的類型解析
- 混合資料類型列的自然解析
- 混合資料類型列的強制解析——IMEX=1
- 如何解決 NULL 值問題
- SQL Server 2000 中的列順序問題
- 如何通路隐藏的 Sheet
- 如何通路非正常命名的 Sheet
接口類型
有兩種接口可供選擇：Microsoft.Jet.OLEDB.4.0（以下簡稱 Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下簡稱 ACE 引擎）。
Jet 引擎大家都很熟悉，可以通路 Office 97-2003，但不能通路 Office 2007。
ACE 引擎是随 Office 2007 一起釋出的資料庫連接配接元件，既可以通路 Office 2007，也可以通路 Office 97-2003。
另外：Microsoft.ACE.OLEDB.12.0 可以通路正在打開的 Excel 檔案，而 Microsoft.Jet.OLEDB.4.0 是不可以的。
Microsoft.ACE.OLEDB.12.0 安裝檔案：
文法一覽
使用 Jet 引擎或 ACE 引擎通路，在文法上沒有什麼的差別。
view plain copy to clipboard print ?
- --> Jet 引擎通路 Excel 97-2003
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
- select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
- select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]
- --> ACE 引擎通路 Excel 97-2003
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
- --> ACE 引擎通路 Excel 2007
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet1$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', [Sheet1$])
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> Jet 引擎通路 Excel 97-2003
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎通路 Excel 97-2003
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎通路 Excel 2007
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
Excel 2007 工作簿檔案的擴充名是：xlsx
HDR=Yes/No
可選參數，指定 Excel 表的第一行是否列名，預設為 Yes，可以在系統資料庫中修改預設的行為。
IMEX=1
可選參數，将 Excel 表中混合 Intermixed 資料類型的列強制解析為文本。
系統資料庫設定
Microsoft.Jet.OLEDB.4.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel
Microsoft.ACE.OLEDB.12.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Office/12.0/Access Connectivity Engine/Engines/Excel
FirstRowHasNames
設定 HDR 參數的預設行為，預設為 Yes。
ImportMixedTypes
設定混合列的強制解析類型，預設為文本 Text。
TypeGuessRows
設定用于解析資料類型的取樣行數，預設取樣前 8 行。如果設定為 0，将分析所有資料行，但不建議這樣做，會影響引擎的性能。
注意：Excel 表資料列是單一列資料類型還是混合列資料類型列，是由取樣行決定，而不是整列資料決定。
單一資料類型列的類型解析
Sheet1 的内容如下圖所示，涵蓋了大部分 Excel 的資料類型，其中 longtext 分别有 256 個 A 和 B。
對于單一資料類型列的類型解析，ACE 引擎和 Jet 引擎是一樣的，下面測試 Jet 引擎的資料解析：
view plain copy to clipboard print ?
- use tempdb
- go
- select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
- select
- a.name,
- date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end
- from
- syscolumns a inner join systypes b on a.xusertype = b.xusertype
- where
- a.id = object_id('#type')
- drop table #type
use tempdb
go
select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
select
a.name,
date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end
from
syscolumns a inner join systypes b on a.xusertype = b.xusertype
where
a.id = object_id('#type')
/*
name date_type
-------- -------------
longtext ntext
text nvarchar(255)
datetime datetime
date datetime
time datetime
money money
float float
numeric float
integer float
*/
drop table #type
資料類型解析總結
- 文本：長度 <= 255，解析為 nvarchar(255)，長度 > 255，解析為 ntext。
- 數值：貨币解析為 money，其它均解析為 float。
- 時間：datetime。
混合資料類型列的自然解析
相對于使用 IMEX=1 的強制解析，不使用 IMEX=1，稱為自然解析。下圖是 Sheet2 的内容：
對于混合資料類型列的自然解析，ACE 引擎和 Jet 有細節上的差別，先看測試：
view plain copy to clipboard print ?
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/2007.xlsx', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 NULL
2 sampling 2 2 NULL
3 sampling 3 3 NULL
4 sampling 4 4 A
5 sampling 5 NULL B
6 sampling NULL NULL C
7 sampling NULL NULL D
8 sampling NULL NULL E
9 others 1 2 NULL
10 others NULL NULL <
----- -------- ------- --------- --------
float nvarchar float float nvarchar <-- 解析的資料類型
*/
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/2007.xlsx', 'select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling NULL B C
7 sampling NULL C D
8 sampling NULL D E
9 others 1 2 3
10 others NULL = <
----- -------- -------- -------- --------
float nvarchar float nvarchar nvarchar <-- 解析的資料類型
*/
相同地方
- 取樣行裡數值型多于文本型，解析為 float 數值。
- 取樣行裡數值型少于文本型，解析為 nvarchar/ntext 文本。
- 當解析為 float 數值時，文本類型顯示為 NULL，這點毫無疑問。
相異地方
- 取樣行裡數值型等于文本型，Jet 引擎解析為 float 數值，數值優先，ACE 引擎解析為 nvarchar/ntext 文本，文本優先。
- 當解析為 nvarchar/ntext 文本時，Jet 引擎将非文本資料顯示為 NULL，ACE 引擎正确顯示。
混合資料類型列的強制解析——IMEX=1
使用 IMEX=1 選參之後，隻要取樣資料裡是混合資料類型的列，一律強制解析為 nvarchar/ntext 文本。當然，IMEX=1 對單一資料類型列的解析是不影響的。
view plain copy to clipboard print ?
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling A B C
7 sampling B C D
8 sampling C D E
9 others 1 2 3
10 others > = <
----- -------- -------- -------- --------
float nvarchar nvarchar nvarchar nvarchar <-- 解析的資料類型
*/
最後一列（num<str），Jet 引擎的自然解析和強制解析都解析為 nvarchar(255)，但是自然解析将數值顯示為 NULL，強制解析卻能正确顯示，這是不一緻的地方。
在資料解析的細節方面，ACE 引擎的表現優于 Jet 引擎。在前面提到的文本優先問題、非文本資料的 NULL 值問題，ACE 引擎的解析更合理。
如何解決 NULL 值問題
前 8 行（取樣行）是混合資料類型的列，使用 IMEX=1 選參解決。
前 8 行是文本，8 行之外有非文本的資料，使用 ACE 引擎解決。
前 8 行是數值，8 行之外又非數值的資料：
- 将前 8 行其中一行的單元格式數字設定為文本（如果還不行，可能要手工重寫該單元格，以應用文本格式，不記得是 Office 97 還是 2000 存在這個問題了）；
- 修改系統資料庫中的 TypeGuessRows（系統資料庫設定），增加取樣行數，或設定為 0 全部解析。
目的隻有一個，讓取樣行變成混合資料類型的列，然後使用 IMEX=1 選參解決。
SQL Server 2000 中的列順序問題
這是 SQL Server 2000 行集函數 OpenRowSet 和 OpenDataSource 本身的問題，與通路接口引擎無關，也與 Excel 版本無關。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在這個問題。
上圖是 Sheet3 的内容，連接配接到 SQL Server 2000 測試看看是什麼問題：
view plain copy to clipboard print ?
- --> HDR=Yes
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', [Sheet3$])
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls')...[Sheet3$]
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]
- --> HDR=No
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', [Sheet3$])
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls')...[Sheet3$]
- select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]
--> HDR=Yes
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', [Sheet3$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls')...[Sheet3$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]
/*
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
C10 C9 C8 C7 C6 C5 C4 C3 C2 C1
*/
--> HDR=No
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', [Sheet3$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls')...[Sheet3$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]
/*
F1 F10 F2 F3 F4 F5 F6 F7 F8 F9
--- --- --- --- --- --- --- --- --- ---
J A I H G F E D C B
C1 C10 C2 C3 C4 C5 C6 C7 C8 C9
*/
傳回結果集的列順序，是按照列名排序，并不是 Excel 表的列順序。HDR=No 貌似正确，但仔細一看，仍然是按列名排序的。
OpenRowSet(query)
OpenRowSet(query) 可以解決這個列順序的問題，包括後面的通路隐藏的 Sheet 或非正常命名的 Sheet，都可以用 OpenRowSet(query) 解決。
view plain copy to clipboard print ?
- --> HDR=Yes
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/2007.xlsx', 'select * from [Sheet3$]')
- --> HDR=No
- select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
- select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/2007.xlsx', 'select * from [Sheet3$]')
--> HDR=Yes
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:/2007.xlsx', 'select * from [Sheet3$]')
/*
J I H G F E D C B A
--- --- --- --- --- --- --- --- --- ---
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
--> HDR=No
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:/2007.xlsx', 'select * from [Sheet3$]')
/*
F1 F2 F3 F4 F5 F6 F7 F8 F9 F10
--- --- --- --- --- --- --- --- --- ---
J I H G F E D C B A
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
如何通路隐藏的 Sheet
隐藏 Sheet 的通路情況比較複雜，就不寫測試過程了，歸納一下：
- 使用 OpenRowSet(query) 肯定可以通路。
- Excel 2007 任何寫法都可以通路（Jet 引擎不能通路 Excel 2007）。
- 打開的 Excel 檔案任何寫法都可以通路（Jet 引擎不能通路打開的 Excel 檔案）。
如何通路非正常命名的 Sheet
建立一個空白的 Sheet，重命名為 4 儲存關閉：
- 使用 OpenRowSet(query) 可以正常通路。
- 其它寫法，用單引号限定名稱 ['4$'] 可以通路。
- OpenRowSet(query) 也可以使用單引号限定通路：'select * from [''4$'']'
引出最後一個問題，通路 Excel 97-2003 空白的 Sheet，會傳回一行 NULL 值，通路 Excel 2007 空白的 Sheet，傳回空結果集，資料類型均解析為 nvarchar(255)。