使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx
- 文档编号:9682276
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:13
- 大小:34.33KB
使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx
《使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx》由会员分享,可在线阅读,更多相关《使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx(13页珍藏版)》请在冰点文库上搜索。
使用OpenRowSet和OpenDataSource访问Excel97
使用OpenRowSet和OpenDataSource访问Excel97-2007
测试文件:
D:
\97-2003.xls和D:
\2007.xlsx,两个文件的内容是一模一样的。
测试环境:
SQLServer2000/2005。
∙接口类型
∙语法一览
∙注册表设置
∙单一数据类型列的类型解析
∙混合数据类型列的自然解析
∙混合数据类型列的强制解析——IMEX=1
∙如何解决NULL值问题
∙SQLServer2000中的列顺序问题
∙如何访问隐藏的Sheet
∙如何访问非常规命名的Sheet
接口类型
有两种接口可供选择:
Microsoft.Jet.OLEDB.4.0(以下简称Jet引擎)和Microsoft.ACE.OLEDB.12.0(以下简称ACE引擎)。
Jet引擎大家都很熟悉,可以访问Office97-2003,但不能访问Office2007。
ACE引擎是随Office2007一起发布的数据库连接组件,既可以访问Office2007,也可以访问Office97-2003。
另外:
Microsoft.ACE.OLEDB.12.0可以访问正在打开的Excel文件,而Microsoft.Jet.OLEDB.4.0是不可以的。
Microsoft.ACE.OLEDB.12.0安装文件:
语法一览
使用Jet引擎或ACE引擎访问,在语法上没有什么的区别。
viewplaincopytoclipboardprint?
1.--> Jet 引擎访问 Excel 97-2003
2.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls', 'select * from [Sheet1$]')
3.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls', [Sheet1$])
4.select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls')...[Sheet1$]
5.select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:
\97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]
6.
7.--> ACE 引擎访问 Excel 97-2003
8.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls', 'select * from [Sheet1$]')
9.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls', [Sheet1$])
10.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\97-2003.xls')...[Sheet1$]
11.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:
\97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
12.
13.--> ACE 引擎访问 Excel 2007
14.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\2007.xlsx', 'select * from [Sheet1$]')
15.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\2007.xlsx', [Sheet1$])
16.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:
\2007.xlsx')...[Sheet1$]
17.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:
\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
Excel2007工作簿文件的扩展名是:
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\AccessConnectivityEngine\Engines\Excel
FirstRowHasNames
设置HDR参数的缺省行为,默认为Yes。
ImportMixedTypes
设置混合列的强制解析类型,默认为文本Text。
TypeGuessRows
设置用于解析数据类型的取样行数,默认取样前8行。
如果设置为0,将分析所有数据行,但不建议这样做,会影响引擎的性能。
注意:
Excel表数据列是单一列数据类型还是混合列数据类型列,是由取样行决定,而不是整列数据决定。
单一数据类型列的类型解析
Sheet1的内容如下图所示,涵盖了大部分Excel的数据类型,其中longtext分别有256个A和B。
对于单一数据类型列的类型解析,ACE引擎和Jet引擎是一样的,下面测试Jet引擎的数据解析:
viewplaincopytoclipboardprint?
1.use tempdb
2.go
3.
4.select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:
\97-2003.xls', 'select * from [Sheet1$]')
5.
6.select
7. a.name,
8. date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end
9.from
10. syscolumns a inner join systypes b on a.xusertype = b.xusertype
11.where
12. a.id = object_id('#type')
13.
14./*
15.name date_type
16.-------- -------------
17.longtext ntext
18.text nvarchar(255)
19.datetime datetime
20.date datetime
21.time datetime
22.money money
23.float float
24.numeric float
25.integer float
26.*/
27.
28.drop table #type
数据类型解析总结
∙文本:
长度<=255,解析为nvarchar(255),长度>255,解析为ntext。
∙数值:
货币解析为money,其它均解析为float。
∙时间:
datetime。
混合数据类型列的自然解析
相对于使用IMEX=1的强制解析,不使用IMEX=1,称为自然解析。
下图是Sheet2的内容:
对于混合数据类型列的自然解析,ACE引擎和Jet有细节上的区别,先看测试:
viewplaincopytoclipboardprint?
1.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:
\97-2003.xls', 'select * from [Sheet2$]')
2./*
3.id describe num>str num=str num 4.----- -------- -------- -------- -------- 5.1 sampling 1 1 NULL 6.2 sampling 2 2 NULL 7.3 sampling 3 3 NULL 8.4 sampling 4 4 A 9.5 sampling 5 NULL B 10.6 sampling NULL NULL C 11.7 sampling NULL NULL D 12.8 sampling NULL NULL E 13.9 others 1 2 NULL 14.10 others NULL NULL < 15.----- -------- ------- --------- -------- 16.float nvarchar float float nvarchar <-- 解析的数据类型 17.*/ 18. 19.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D: \97-2003.xls', 'select * from [Sheet2$]') 20.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D: \2007.xlsx', 'select * from [Sheet2$]') 21./* 22.id describe num>str num=str num 23.----- -------- -------- -------- -------- 24.1 sampling 1 1 1 25.2 sampling 2 2 2 26.3 sampling 3 3 3 27.4 sampling 4 4 A 28.5 sampling 5 A B 29.6 sampling NULL B C 30.7 sampling NULL C D 31.8 sampling NULL D E 32.9 others 1 2 3 33.10 others NULL = < 34.----- -------- -------- -------- -------- 35.float nvarchar float nvarchar nvarchar <-- 解析的数据类型 36.*/ 相同地方 ∙取样行里数值型多于文本型,解析为float数值。 ∙取样行里数值型少于文本型,解析为nvarchar/ntext文本。 ∙当解析为float数值时,文本类型显示为NULL,这点毫无疑问。 相异地方 ∙取样行里数值型等于文本型,Jet引擎解析为float数值,数值优先,ACE引擎解析为nvarchar/ntext文本,文本优先。 ∙当解析为nvarchar/ntext文本时,Jet引擎将非文本数据显示为NULL,ACE引擎正确显示。 混合数据类型列的强制解析——IMEX=1 使用IMEX=1选参之后,只要取样数据里是混合数据类型的列,一律强制解析为nvarchar/ntext文本。 当然,IMEX=1对单一数据类型列的解析是不影响的。 viewplaincopytoclipboardprint? 1.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D: \97-2003.xls', 'select * from [Sheet2$]') 2.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D: \97-2003.xls', 'select * from [Sheet2$]') 3.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D: \2007.xlsx', 'select * from [Sheet2$]') 4./* 5.id describe num>str num=str num 6.----- -------- -------- -------- -------- 7.1 sampling 1 1 1 8.2 sampling 2 2 2 9.3 sampling 3 3 3 10.4 sampling 4 4 A 11.5 sampling 5 A B 12.6 sampling A B C 13.7 sampling B C D 14.8 sampling C D E 15.9 others 1 2 3 16.10 others > = < 17.----- -------- -------- -------- -------- 18.float nvarchar nvarchar nvarchar nvarchar <-- 解析的数据类型 19.*/ 最后一列(num 在数据解析的细节方面,ACE引擎的表现优于Jet引擎。 在前面提到的文本优先问题、非文本数据的NULL值问题,ACE引擎的解析更合理。 如何解决NULL值问题 前8行(取样行)是混合数据类型的列,使用IMEX=1选参解决。 前8行是文本,8行之外有非文本的数据,使用ACE引擎解决。 前8行是数值,8行之外又非数值的数据: ∙将前8行其中一行的单元格式数字设置为文本(如果还不行,可能要手工重写该单元格,以应用文本格式,不记得是Office97还是2000存在这个问题了); ∙修改注册表中的TypeGuessRows(注册表设置),增加取样行数,或设置为0全部解析。 目的只有一个,让取样行变成混合数据类型的列,然后使用IMEX=1选参解决。 SQLServer2000中的列顺序问题 这是SQLServer2000行集函数OpenRowSet和OpenDataSource本身的问题,与访问接口引擎无关,也与Excel版本无关。 SQLServer2005的OpenRowSet和OpenDataSource不存在这个问题。 上图是Sheet3的内容,连接到SQLServer2000测试看看是什么问题: viewplaincopytoclipboardprint? 1.--> HDR=Yes 2.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D: \97-2003.xls', [Sheet3$]) 3.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D: \97-2003.xls')...[Sheet3$] 4.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D: \2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$] 5./* 6.A B C D E F G H I J 7.--- --- --- --- --- --- --- --- --- --- 8.C10 C9 C8 C7 C6 C5 C4 C3 C2 C1 9.*/ 10. 11.--> HDR=No 12.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D: \97-2003.xls', [Sheet3$]) 13.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D: \97-2003.xls')...[Sheet3$] 14.select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D: \2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$] 15./* 16.F1 F10 F2 F3 F4 F5 F6 F7 F8 F9 17.--- --- --- --- --- --- --- --- --- --- 18.J A I H G F E D C B 19.C1 C10 C2 C3 C4 C5 C6 C7 C8 C9 20.*/ 返回结果集的列顺序,是按照列名排序,并不是Excel表的列顺序。 HDR=No貌似正确,但仔细一看,仍然是按列名排序的。 OpenRowSet(query) OpenRowSet(query)可以解决这个列顺序的问题,包括后面的访问隐藏的Sheet或非常规命名的Sheet,都可以用 OpenRowSet(query)解决。 viewplaincopytoclipboardprint? 1.--> HDR=Yes 2.select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D: \97-2003.xls', 'select * from [Sheet3$]') 3.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D: \97-2003.xls', 'select * from [Sheet3$]') 4.select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D: \2007.xlsx'
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 使用 OpenRowSet OpenDataSource 访问 Excel 97