EXCEL文件的导入与导出

--==============================================
作者：王运亮(wwwwgou)
时间：2011-06-08
博客：http://blog.csdn.net/wwwwgou
--==============================================

--开启导入配置
sp_configure 'show advanced options',1 
RECONFIGURE WITH override 
GO
sp_configure 'Ad Hoc Distributed Queries',1 --1:启用(默认) 0:禁用 
RECONFIGURE WITH override 
GO
EXEC sp_configure;  --显示所有配置信息
GO

--导入(例子中需要加上insert into/into语句)
--#1.直接引用表名即可
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls')...[test$]
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls',[test$])
--#2.数字或不规则表名需要加'限定
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls')...['Auto Add$']
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls',['3$'])
--#3.用这种方式可免除#2中的限定
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls','select * from [3$]')
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls','select * from [Auto Add$]')
GO
--#4.BCP导入(文本文件)
EXEC master..xp_cmdshell 'bcp TEST.dbo.TEST in D:/test.txt -c -F 10 -L 13 -T'
EXEC master..xp_cmdshell 'bcp TEST.dbo.TEST in D:/test.txt -F 10 -L 13 -c -x -f c:/test.xml -T'
GO

--开启导出配置
sp_configure 'show advanced options',1 
RECONFIGURE WITH override 
GO
sp_configure 'xp_cmdshell',1 -- 1:启用(默认) 0:禁用 
RECONFIGURE WITH override 
GO 
EXEC sp_configure; 
GO 

--导出
--#1.使用OPENROWSET
INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','excel 8.0; HDR=YES;database=D:/test.xls',['3$'])
(
 [level 1],
 [level 2]
)
SELECT
 'x',
 'y'

--#2.BCP导出.注意: 用BCP导出的是类Excel文件,其实质为文本文件
--#2.1 BCP带连接信息导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp Test.dbo.B out "D:/test.xls" -c -S"实例名" -U"sa" -P"1q2w3e4R"'  

--#2.2 BCP使用默认安全连接导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp Test.dbo.B out "D:/test1.xls" -c -T'  

--#2.3 BCP带查询语句导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp "SELECT TOP(1) * FROM Test.dbo.B order by code" queryout "D:/test2.xls" /c -S"实例名" -U"sa" -P"1q2w3e4R"'

--#2.4
EXEC master..xp_cmdshell 'bcp Test.dbo.B format nul -f D:/test.xml -x -c -T'
GO
/*
BCP共有四个动作可以选择。
(1) 导入 : 这个动作使用in命令完成，后面跟需要导入的文件名。
(2) 导出 : 这个动作使用out命令完成，后面跟需要导出的文件名。
(3) 使用SQL语句导出 : 这个动作使用queryout命令完成，它跟out类似，只是数据源不是表或视图名，而是SQL语句。
(4) 导出格式文件 : 这个动作使用format命令完成，后而跟格式文件名。
常用参数介绍:
-f : format_file表示格式文件名。这个选项依赖于上述的动作，如果使用的是in或out，format_file表示已经存在的格式文件，如果使用的是format则表示是要生成的格式文件。
-x : 这个选项要和-f format_file配合使用，以便生成xml格式的格式文件。
-F : first_row指定从被导出表的哪一行导出，或从被导入文件的哪一行导入。
-L : last_row指定被导出表要导到哪一行结束，或从被导入文件导数据时，导到哪一行结束。
-c : 使用char类型做为存储类型，没有前缀且以"/t"做为字段分割符，以"/n"做为行分割符。
-w : 和-c类似，只是当使用Unicode字符集拷贝数据时使用，且以nchar做为存储类型。
-t : field_term指定字符分割符，默认是"/t"。
-r : row_term指定行分割符，默认是"/n"。 
-S : server_name指定要连接的SQL Server服务器的实例，如果未指定此选项，BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例，只需要指定机器名即可。
-U : login_id指定连接SQL Sever的用户名。
-P : password指定连接SQL Server的用户名密码。
-T : 指定BCP使用信任连接登录SQL Server。如果未指定-T，必须指定-U和-P。
-k : 指定空列使用null值插入，而不是这列的默认值。
*/