将Access、Excel数据导出到MSSQL/使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007
使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007
测试文件：D:/97-2003.xls和D:/2007.xlsx，两个文件的内容是一模一样的。
测试环境：SQL Server 2000 / 2005。
接口类型
有两种接口可供选择：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 引擎访问，在语法上没有什么的区别。
1 --> Jet 引擎访问 Excel 97-2003 2 3 select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]') 4 5 select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$]) 6 7 select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$] 8 9 select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$] 10 11 12 13 --> ACE 引擎访问 Excel 97-2003 14 15 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]') 16 17 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$]) 18 19 select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$] 20 21 select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$] 22 23 24 25 --> ACE 引擎访问 Excel 2007 26 27 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet1$]') 28 29 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', [Sheet1$]) 30 31 select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$] 32 33 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 引擎的数据解析：
-
1 use tempdb 2 3 go 4 5 6 7 select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet1$]') 8 9 10 11 select 12 13 a.name, 14 15 date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end 16 17 from 18 19 syscolumns a inner join systypes b on a.xusertype = b.xusertype 20 21 where 22 23 a.id = object_id('#type') 24 25 26 27 /* 28 29 name date_type 30 31 -------- ------------- 32 33 longtext ntext 34 35 text nvarchar(255) 36 37 datetime datetime 38 39 date datetime 40 41 time datetime 42 43 money money 44 45 float float 46 47 numeric float 48 49 integer float 50 51 */ 52 53 54 55 drop table #type
数据类型解析总结
-
文本：长度 <= 255，解析为 nvarchar(255)，长度 > 255，解析为 ntext。
-
数值：货币解析为 money，其它均解析为 float。
-
时间：datetime。
混合数据类型列的自然解析
相对于使用 IMEX=1 的强制解析，不使用 IMEX=1，称为自然解析。下图是 Sheet2 的内容：
对于混合数据类型列的自然解析，ACE 引擎和 Jet 有细节上的区别，先看测试：
[sql]
-
1 select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]') 2 3 /* 4 5 id describe num>str num=str num<str 6 7 ----- -------- -------- -------- -------- 8 9 1 sampling 1 1 NULL 10 11 2 sampling 2 2 NULL 12 13 3 sampling 3 3 NULL 14 15 4 sampling 4 4 A 16 17 5 sampling 5 NULL B 18 19 6 sampling NULL NULL C 20 21 7 sampling NULL NULL D 22 23 8 sampling NULL NULL E 24 25 9 others 1 2 NULL 26 27 10 others NULL NULL < 28 29 ----- -------- ------- --------- -------- 30 31 float nvarchar float float nvarchar <-- 解析的数据类型 32 33 */ 34 35 36 37 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/97-2003.xls', 'select * from [Sheet2$]') 38 39 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:/2007.xlsx', 'select * from [Sheet2$]') 40 41 /* 42 43 id describe num>str num=str num<str 44 45 ----- -------- -------- -------- -------- 46 47 1 sampling 1 1 1 48 49 2 sampling 2 2 2 50 51 3 sampling 3 3 3 52 53 4 sampling 4 4 A 54 55 5 sampling 5 A B 56 57 6 sampling NULL B C 58 59 7 sampling NULL C D 60 61 8 sampling NULL D E 62 63 9 others 1 2 3 64 65 10 others NULL = < 66 67 ----- -------- -------- -------- -------- 68 69 float nvarchar float nvarchar nvarchar <-- 解析的数据类型 70 71 */
相同地方
-
取样行里数值型多于文本型，解析为 float 数值。
-
取样行里数值型少于文本型，解析为 nvarchar/ntext 文本。
-
当解析为 float 数值时，文本类型显示为 NULL，这点毫无疑问。
相异地方
-
取样行里数值型等于文本型，Jet 引擎解析为 float 数值，数值优先，ACE 引擎解析为 nvarchar/ntext 文本，文本优先。
-
当解析为 nvarchar/ntext 文本时，Jet 引擎将非文本数据显示为 NULL，ACE 引擎正确显示。
混合数据类型列的强制解析——IMEX=1
使用 IMEX=1 选参之后，只要取样数据里是混合数据类型的列，一律强制解析为 nvarchar/ntext 文本。当然，IMEX=1 对单一数据类型列的解析是不影响的。
-
1 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]') 2 3 select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet2$]') 4 5 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet2$]') 6 7 /* 8 9 id describe num>str num=str num<str 10 11 ----- -------- -------- -------- -------- 12 13 1 sampling 1 1 1 14 15 2 sampling 2 2 2 16 17 3 sampling 3 3 3 18 19 4 sampling 4 4 A 20 21 5 sampling 5 A B 22 23 6 sampling A B C 24 25 7 sampling B C D 26 27 8 sampling C D E 28 29 9 others 1 2 3 30 31 10 others > = < 32 33 ----- -------- -------- -------- -------- 34 35 float nvarchar nvarchar nvarchar nvarchar <-- 解析的数据类型 36 37 */
最后一列（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 测试看看是什么问题：
-
--> 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) 解决。
-
--> 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)。
