将Access、Excel数据导出到MSSQL/使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007-白红宇

将Access、Excel数据导出到MSSQL/使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

阅读量：5846 次

发布时间：2019-06-18

本文共 8498 字，大约阅读时间需要 28 分钟。

使用 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 引擎访问，在语法上没有什么的区别。

[sql]

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 引擎的数据解析：

[sql]

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  num=str  num

相同地方

取样行里数值型多于文本型，解析为 float 数值。

取样行里数值型少于文本型，解析为 nvarchar/ntext 文本。

当解析为 float 数值时，文本类型显示为 NULL，这点毫无疑问。

相异地方

取样行里数值型等于文本型，Jet 引擎解析为 float 数值，数值优先，ACE 引擎解析为 nvarchar/ntext 文本，文本优先。

当解析为 nvarchar/ntext 文本时，Jet 引擎将非文本数据显示为 NULL，ACE 引擎正确显示。

混合数据类型列的强制解析——IMEX=1

使用 IMEX=1 选参之后，只要取样数据里是混合数据类型的列，一律强制解析为 nvarchar/ntext 文本。当然，IMEX=1 对单一数据类型列的解析是不影响的。

[sql]

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
       
                =        <  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 测试看看是什么问题：

[sql]

--> 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) 解决。

[sql]

--> 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  */