Excel数据导入到Sql Server 2005

一、如果导入时报如下错误

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

按照提示翻了一下SQL 2005的联机丛书,得知启用Ad Hoc Distributed Queries的方法,具体如下:

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

使用完毕后,记得要关闭它,因为这是一个安全隐患

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

二、导入过程

  1. 远程导入会报错,只能本机导入,或者远程桌面连接;

  2. xcel文件中有一时间列,混合了文本与时间,如 傍晚 凌晨 12:15 其中hh:mm居多,使用DTSWizard导入虽然方便但是hh:mm都变成了NULL,在导入设置中设置导入数据类型不能实现。

需要语句实现,下面的语句都可以实现:

(1)

SELECT * into newtable1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:\yyyy_wind.xls";User ID=;Password=;Extended properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$  

(2)

SELECT * into newtable2 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource=C:\yyyy_wind.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$  

(3)

SELECT * into newtable3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\yyyy_wind.xls;HDR=YES;IMEX=1', sheet1$ )  

(4)

INSERT INTO question_bank SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:\jyy.xls";User ID=;Password=;Extended properties="Excel 8.0;HDR=NO;IMEX=1"')...sheet2$   

(5)

SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:\jyy.xls";User ID=;Password=;Extended properties="Excel 8.0;HDR=NO;IMEX=1"')...sheet2$  

注意:完全可以把sheet作为一个表看待,后面可以跟where,order by 等条件语句

这sheet指的是excel默认的sheet1,可以修改,文件路径取实际路径。

网上其实好多资料,但是由于使用OpenDataSource和OPENROWSET都可以实现,又加上双引号" "单引号''混用,难以搞清楚。关键问题是这样的使用OpenDataSource时,Extended Properties的内容分号间隔并用双引号括起来,sheet1$ 在括号外,OPENROWSET相对简洁.

  1. 下面介绍HDR和IMEX两个参数

HDR为YES,excel文件的首行作为sqlserver表的列名,为NO则作表的第一行。
IMEX=1 意思是 tells the driver to always read "intermixed" data columns as text
在这里driver的意思是excel的驱动程序。

Excel 驱动程序读取指定源中一定数量的行(默认情况下为 8 行)以推测每列的数据类型。如果推测出列可能包含混合数据类型(尤其是混合了文本数据的数值数据时),驱动程序将决定采用占多数的数据类型,并对包含其他类型数据的单元返回空值。(如果各种数据类型的数量相当,则采用数值类型。)

Excel 工作表中大部分单元格格式设置选项不会影响此数据类型判断。(有说在写入excel表时就全部设定为文本,可以,囧!)

SQLSERVER2005由于安全性的提高,直接使用上述语句还是有问题的(2000可以直接用)。

  1. 按提示进行如下操作:
    从开始菜单进入到 配置工具 > sql server 2005 外围应用配置器 > 服务和连接的外围配置器> Datebase Engine 启动服务,然后重启sqlserver服务。由于sql server 2005没有了2000那样任务栏右下角的图表,所以需要通过控制面板进入管理工具 >服务 停止 后启动 sqlserver 服务。

然后运行 regedit 进入注册表编辑器 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 新建DWORD 名为DisallowAdhocAccess 值为0.

/usr/themes/NexTSun/static/images/yovisun-weixin-share.jpg
赞 (0) 分享
声明:原创文章,欢迎转载,请以 超链接 的形式注明 作者标题原始出处查看许可协议
标题Excel数据导入到Sql Server 2005 | 作者:YoviSun
地址http://www.yovisun.com/archive/excel-sql-server-2005.html
相关文章:
添加新评论
选择表情
手机扫描二维码访问