链接服务器——获取EXCEL数据
来源:互联网 发布:淘宝怎么买话费充值 编辑:程序博客网 时间:2024/05/16 13:51
测试目的:验证利用链接服务器、分布式查询获取EXCEL中的数据
测试环境:
Microsoft SQL Server 2005 - 9.00.3080.00 (X64)
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
基础知识及相关准备:
1、基于OLE DB访问的相关基础知识
2、创建链接服务器:sp_addlinkedserver
3、Openrowset
基于OLE DB访问的相关基础知识,参看下图
本次测试相关接口为:Microsoft OLE DB Provider for Jet
针对Excel不同版本请参考下表使用不同的OLE DB接口参数
Office Version Provider Provider_String
Office 97 ~2005 Microsoft.Jet.OLEDB.4.0 Excel 5.0
Office 2007 Microsoft.ACE.OLEDB.12.0 Excel 12.0[需安装组件或打ServicePack 1.0]
创建链接服务器
DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)SET @server = 'XLTEST_SP'SET @srvproduct = 'Excel'SET @datasrc = 'c:\book1.xls'--注意因excel的版本不同,选择不同的接口参数SET @provider = 'Microsoft.Jet.OLEDB.4.0' SET @provstr = 'Excel 8.0'EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog--获取链接服务器对应sheet1范围内的数据SELECT * FROM [Excel]...[Sheet1$]--针对Microsoft.ACE.OLEDB.12.0,还需告知SQL Server如果处理USE [master]GOEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'AllowInProcess',1GOEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'DynamicParameters',1GOOpenRowSetEXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOselect * into NewTableFROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0;Database=c:\book1.xlsx;HDR=YES' , 'SELECT * FROM [sheet1$]' )END
经测试可获得对应excel工作表中的数据。
测试中的问题点及部分解决办法:
1、对应office97~2005创建链接服务器时,无对应的OLE DB 接口组件。
【解决方法】:
安装office2005内版本即可获取到Microsoft.Jet.OLEDB.4.0接口组件。安装在数据源中可能无法获取到对应安装的文件。 还请在运行中使用regsvr32 msjetoledb40.dll 对其进行模块注册即可。
【问题】:
Microsoft.Jet.OLEDB.4.0 至sp8后无更新的版本,且对应64位没有对应的更新。(据坛子里的朋友说有过64位的补丁,据现有查证没有找到)
建议:因Microsoft.Jet.OLEDB.4.0不再进行更新支持,对应于Office相关链接组件还请根据最新的office版本进行下载更新。
2、SQL Server 2005中对应Office2007类型文件,无对应OLE DB接口组件。
【解决办法】:安装2007 Office system 驱动程序:数据连接组件即可。
下载地址:点击打开链接
3、对应于查找范围的一个小BUG
[Sheet1$]类似于此的查找范围,如果首字符为非英文字符时,分布式查询及链接服务器引用方式均无法显示的获得对应范围的数据。
不知是我的配置问题导致,还是都有的BUG还请查证。
4、针对提示RPC的问题
【解决办法】:
USE master;EXEC sp_serveroption '服务器名称', 'rpc', 'true'; --启用RPC
5、OLE DB 提供程序 'SQLOLEDB' 指出该对象中没有任何列
【解决方法】:
set fmtonly off 设置即可。
参考文档:
1、如何将数据从 Excel 导入到 SQL Server
2、How to use Excel with SQL Server linked servers and distributed queries
3、PRB:当运行使用 OLE DB Provider for Microsoft Jet 的链接服务器查询时,出现错误 7399
4、关于 Jet 4.0 Service Pack 8 的信息
5、2007 Office system 驱动程序:数据连接组件
- 链接服务器——获取EXCEL数据
- 链接服务器——获取EXCEL数据
- 链接服务器获取EXCEL数据
- 使用链接服务器获取远程服务器数据
- 链接服务器访问Excel
- C# 操作Excel ——Excel获取数据、时间、图片
- java httpurlconnection链接 lnmp服务器传输与获取数据
- 用Excel获取数据——不仅仅只是打开表格
- Excel 数据导入--服务器
- 【SQL Server数据迁移】链接服务器、分布式查询:SQL Server、ORACLE、Excel、txt、Access
- C#获取Excel数据
- QTP获取EXCEl数据
- 获取Excel表格数据
- paython 获取excel数据
- 完美获取Excel数据
- 建立服务器对象——链接服务器
- 哈希图像检索—图像数据集获取链接总结【Hashing数据集】
- SQL2008链接服务器数据同步
- 一个不错的JS网站
- 如何将Dock移到外接显示屏中
- 项目经理面试
- asp.net面试题
- TortoiseSVN每个菜单项都表示什么意思
- 链接服务器——获取EXCEL数据
- vc 对话框修改菜单栏的背景颜色
- 教你怎么样用U盘给苹果 MacBook Air电脑装Win7
- Windows 消息综述/WM_USER/WM_APP
- 【平衡二叉树】Who Gets the Most Candies?
- 怎样判断ios app 第一次启动
- dos命令
- Android进程与线程基本知识
- smali文件语法参考