将Excel数据导入到SQL Server数据库中（收集1）

一：

将Excel数据导入到SQL Server数据库中
1）接受数据导入的表已经存在。
insert into t1 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,
'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);
2）导入数据并生成表。
select * into t1 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);

3) 导入Excel中指定的列到数据库表中指定的列。
INSERT INTO t1(a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET 'MICROSOFT.JET.OLEDB.4.0' ,'Excel5.0; HDR=YES; DATABASE=c:\\test.xls',sheet1$);

需要注意的地方。
1）外围应用配置器的设置。
从“功能外围应用配置器”中选择“启动 OPENROWSET 和 OPENDATASOURCE 支持”选项。
2）关闭Excel表。
如果在导入时要导入的Excel表格处于打开状态，会提示：
“无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "microsoft.jet.oledb.4.0" 的数据源对象。”
3）导入数据时，Excel的首行会作为表头，若导入到已存在的数据库表，则忽略首行。

 

二：

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

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\HaierWeb\MyWeb\Doc\abc.xls;Extended Properties=Excel 8.0')...Sheet1$

第一行当做表头。

其余行当成一种数据类型，且不能转换，如果有文本，有数字， 按出现多的一项显示，其余为Null

员工信息　　F2

NULL 　　姓名
664754 张三
664783 李四

Null 处应该是 员工编号 。 前两行是表头。

对于这种形式，应该把 数字前多输一个’（单引号） 强行转换为文本格式。快速填入相同内容 选中多个单元格后，输入字符，然后按Ctrl+Enter组合键，即可在选中的每个单元格中填入上述相同的字符。

解决方法：

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\HaierWeb\MyWeb\Doc\abc.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"')...Sheet1$

HDR=Yes , 是说第一列当做表头。

IMEX=1 , 是说把表内容混合读取。

IMEX=2 , 是指可以对表内容进行 读取 和 写入.

在OleDb中,如果不存在该Excel,创建表时,自动创建Excel文件.

Jet 居然支持这么多数据库， 连Html table 也支持。 参考：http://zh.wikipedia.org/zh-cn/Microsoft_Jet_Database_Engine

查询文本文件:

文件必须是,号分割文件

HDR=YES 的时候,第一行被认为是字段名
HDR=NO 的时候,第一行被认为是数据

select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=NO;DATABASE=C:\' --c:\是目录
,Surveys#txt)--aa#txt是文本文件名aa.txt

eg： insert into temp_qdfd_lylpxm(zqsj,xmid,xmmc,xmdz,qymc,szsq,zts,zmj,zzts,zzmj,yszts,yszmj,yszzts,yszzmj,jj)
select zqsj,xmid,xmmc,xmdz,qymc,szsq,zts,zmj,zzts,zzmj,yszts,yszmj,yszzts,yszzmj,jj
from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=d:\\2011年1月上.xls',sheet1$);

2007eg：insert into temp_qdfd_lylpxmkpdyxshxx(xmmc,bz,ldmc,sh,ycjzmj,yctnmj,ycftmj,zt,fx,fwlx,zqsj)
select xmmc,bz,ldmc,sh,ycjzmj,jctnmj,jcftmj,zt,fx,fwlx,zqsj
from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;DATABASE=d:\\户型1212.xlsx',sheet1$);