Excel 2003 && 2007 导入SQL Server 2008

Excel 2003 导入SQL Server 2008

这两个导入语句需把Microsoft.Jet.OLEDB.4.0的属性中的动态参数和允许进程内勾去掉，即“访问接口选项”全不选：

SELECT * into test2003update1

FROMOpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="E:\test.xls";

  User ID=Admin;Password=;Extended properties="Excel 5.0;

  HDR=Yes;IMEX=1;"')...[test$];

 

insert into test2003update1

select * FROM OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="E:\test.xls";

  User ID=Admin;Password=;Extended properties="Excel 5.0;

  HDR=Yes;IMEX=1;"')...[test$];

clip_image001

clip_image002

如果选择了动态参数和允许进程内这两个属性,运行会报7303的消息

clip_image004

这个导入语句需把Microsoft.Jet.OLEDB.4.0的属性中的动态参数和允许进程内勾去掉，即“访问接口选项”全不选：

clip_image005

如果选择了动态参数和允许进程内这两个属性,运行会报7303的消息

 

clip_image007

Excel 2007导入SQL Server 2008

select * into test2007

FROM OPENROWSET (

  'Microsoft.ACE.OLEDB.12.0' ,

  'Excel 12.0;Database=E:\Test1.xlsx;HDR=YES;IMEX=1;' ,

  'SELECT * FROM [test1$]' )

 

insert into testupdate1

select * FROM OPENROWSET (

  'Microsoft.ACE.OLEDB.12.0' ,

  'Excel 12.0;Database=E:\Test1.xlsx;HDR=YES;IMEX=1;' ,

  'SELECT * FROM [test1$]' )

这两个导入语句需把Microsoft.ACE.OLEDB.12.0的属性中的动态参数和允许进程内勾去掉，即“访问接口选项”全不选：

clip_image009

如把动态参数和允许进程内，这两个属性选中，运行上面相同的导入语句（into后面的表名不能同名了）会报错：消息7399和消息7303

select * into testupdate1

FROM OPENROWSET (

  'Microsoft.ACE.OLEDB.12.0' ,

  'Excel 12.0;Database=E:\Test1.xlsx;HDR=YES;IMEX=1;' ,

  'SELECT * FROM [test1$]' )

 

clip_image011

把两个属性去掉，再次使用刚刚报错的导入语句，OK

clip_image013

如果纯粹是是查询，没有into，这两个属性选不选都可以正常运行：

clip_image014

下面的两个语句实现和上面的实现效果一样，同样需要把动态参数和允许进程内不选中；这两个语句是参考techne上的Excel与SQL Server数据交互：http://social.technet.microsoft.com/Forums/zh-CN/d6748e0b-ae54-44a8-986c-031ddcf74e23/excelsql-server?forum=BIDA

 

INSERT INTO test.dbo.testupdate1

SELECT *

FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=E:\test.xlsx','SELECT * FROM [test$]');

 

Select * INTO testupdate6

FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=E:\test.xlsx','SELECT * FROM [test$]');

其他问题

1.  动态参数和允许进程内，这个两个属性选中，select * into 和 insert into select*可能会一直运行，也没有报错。我打开SSMS后，设置两个属性后，运行就一直运行（运行了一两分钟后我没耐心等了，就取消，还是显示正在取消），最后关闭整个SSMS，断开连接

2.  上面的语句和用户没有关系，或者应该说和sa账户或windows系统验证的身份没关系

3.  很多莫名其妙的报错，是你语句写的问题，比如我select * into from 这个是可以正常运行的，我使用insert into select from 就提示我未注册Microsoft.ACE.OLEDB.12.0，这个报错肯定不对啊，我如果未注册的话，select * into from肯定也不行。我把之前的OPENROWSET语句复制过去修改就没有问题了。

4.  就那几句话，也没有几个设置，下面把从网上搜到的相当完整设置粘贴出来，也为以后自己再遇到问题时方便处理，《在 SQL Server 中查询EXCEL 表中的数据遇到的各种问题》，链接：http://blog.csdn.net/kk185800961/article/details/9186335

Excel 2003和2007设置一样

[sql] view plaincopyprint?

1.  SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]    

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 

问题：
消息 15281，级别 16，状态 1，第 1 行
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

解决：

[sql] view plaincopyprint?

1.  exec sp_configure 'show advanced options',1  

2.  reconfigure 

3.  GO 

4.  exec sp_configure 'Ad Hoc Distributed Queries',1 

5.  reconfigure 

6.  GO 

7.    

8.   

9.  SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]   

10. 

11. 

12.--exec sp_configure 'Ad Hoc Distributed Queries',0 

13.--reconfigure 

14.--GO 

15.--exec sp_configure 'show advanced options',0 

16.--reconfigure  

17.--GO  

exec sp_configure 'show advanced options',1 
reconfigure
GO
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
GO


 


SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]  


 


 


--exec sp_configure 'Ad Hoc Distributed Queries',0


--reconfigure


--GO


--exec sp_configure 'show advanced options',0


--reconfigure 


--GO


问题：


消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。
消息 7303，级别 16，状态 1，第 1 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。


解决：


对象资源管理器——>服务器对象——>连接服务器——>访问接口——>Microsoft.Jet.OLEDB.4.0——>(右键属性，接口选项全不启用)
clip_image015


 


问题：


消息 7302，级别 16，状态 1，第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "MSDASC" 的实例。


 


解决：


将SQL Server（MSSQLSERVER）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）两个服务的登录身份改为LocalSystem


clip_image016


 


问题：


链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "外部表不是预期的格式。"。
消息 7303，级别 16，状态 1，第 1 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。


解决：


Microsoft.Jet.OLEDB.4.0是Microsoft Jet引擎，这适用于2003版本。在2007中，微软对其旗下 Access 与 Excel 的主要文件格式进行修改，并且重命名为 .accdb（Access 2007 数据库文件）与 .xlsx（Excel 2007 文件），因此未被 Microsoft Jet 引擎所支持，不过微软也很快的提出了Microsoft Office 2007 Desktop Drivers: Data Connectivity Components来支持，目前的解决方法就是把连接字符串中的数据提供者改为 Microsoft.ACE.OLEDB.12.0。


下载安装： Microsoft Office 2007 Desktop Drivers: Data Connectivity Components   http://www.microsoft.com/zh-cn/download/details.aspx?id=23734


SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]  


 


问题：


消息 7314，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 不包含表 "Sheet1$"。该表不存在，或者当前用户没有访问该表的权限。


 


最终解决：


在网上搜索一番，终于找到原因了！


原来我的excel列表名字不是Sheet1，而是 KK！所以上面的所有  Sheet1$ 都改为 KK$!!


clip_image017clip_image018


完整代码如下（两中写法）：


[sql] view plaincopyprint?


1.  --  


2.    


3.    


4.  exec sp_configure 'show advanced options',1   


5.  reconfigure  


6.  GO  


7.  exec sp_configure 'Ad Hoc Distributed Queries',1  


8.  reconfigure  


9.  GO  


10.   


11.  


12.SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[KK$]    


13.SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[KK$]    


14.  


15.SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\KK.xls','SELECT * FROM [KK$]')  


16.select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\KK.xlsx;HDR=YES','SELECT * FROM [KK$]')    


17.  


18.  


19.exec sp_configure 'Ad Hoc Distributed Queries',0  


20.reconfigure  


21.GO  


22.exec sp_configure 'show advanced options',0  


23.reconfigure   


24.GO  


25.  


26.  


27.--  


--


 


 


exec sp_configure 'show advanced options',1 


reconfigure


GO


exec sp_configure 'Ad Hoc Distributed Queries',1


reconfigure


GO




 


SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[KK$]  


SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[KK$]  


 


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\KK.xls','SELECT * FROM [KK$]')


select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\KK.xlsx;HDR=YES','SELECT * FROM [KK$]')  


 


 


exec sp_configure 'Ad Hoc Distributed Queries',0


reconfigure


GO


exec sp_configure 'show advanced options',0


reconfigure 


GO


 


 


--



------------------------------------------------------------------------------------------------------------------------------------------------------------ 


------------------------------------------------------------------------------------------------------------------------------------------------------------


成功之后，试回原来的操作：


 


1. 以下这个对Microsoft.Jet.OLEDB.4.0没有影响,对 Microsoft.ACE.OLEDB.12.0 有影响(接口选项要全不启用)：


对象资源管理器——>服务器对象——>连接服务器——>访问接口——>Microsoft.Jet.OLEDB.4.0——>(右键属性，接口选项全不启用)


 


 


2. 以下两个服务身份由LocalSystem个改回 NetWord Service ，对Microsoft.Jet.OLEDB.4.0没有影响,对 Microsoft.ACE.OLEDB.12.0 有影响


将SQL Server（MSSQLSERVER）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）两个服务的登录身份改为LocalSystem


 


SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[KK$]  


错误：


消息 7302，级别 16，状态 1，第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "MSDASC" 的实例。


 


select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\KK.xlsx;HDR=YES','SELECT * FROM [KK$]') 


错误：


消息 7302，级别 16，状态 1，第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 的实例。


 


5.遇到问题再搜索，不断的错误积累才能成功！其实没有自己的亲身体会谁也不知道别人写的对不对，开始我按照technet上的处理还报错呢！后来自己成功导入了，再把technet上的语句复制下来修改，竟然也成功了。很多资料写的都没错，只是他们相应的设置没写出来的，而我们恰好也没设置！






            

            
posted @ 
2014-04-29 15:17 
cnmarkao 
阅读(1753) 
评论(2) 
编辑 
收藏 
举报