SQLServer与excel和txt的导入导出

exec sp_configure 'show advanced options',1 --启用高级配置选项设置
reconfigure;
--reconfigure with override; --reconfigure不行则都用这个
exec sp_configure 'Ad Hoc Distributed Queries',1  --启用 OPENROWSET,OpenDataSource
reconfigure;
exec sp_configure 'xp_cmdshell',1 --启用xp_cmdshell
reconfigure;

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

--【导入excel】
/*
服务器对象-->链接服务器-->访问接口-->Microsoft.Jet.OLEDB.4.0（右键属性）-->把[允许进程内]前面的钩去掉。
HDR: 表示第一行是否字段名,默认是YES
IMEX: 0/1/2/,分别为 导出/导入/混合模式,

*/

select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=F:\kk1.xls',sheet1$)
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=F:\kk1.xls',sheet2$)

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

【出现此错误，把Excel表打开再运行上面的语句。】
链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "外部表不是预期的格式。"。
'消息 7303，级别 16，状态 1，第 1 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。'



--【导出excel】
EXEC master..xp_cmdshell 'bcp test..tb out F:\out.xls -c -Slocalhost -Usa -Psa'
EXEC master..xp_cmdshell 'bcp "Select * from test..tb" queryout F:\out.xls -c -Slocalhost -Usa -Psa'

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

--【导入TXT】注意：txt 文件中无列名，都为内容。

--  drop table temp
create table temp(ID INT,NAME VARCHAR(10))

BULK INSERT temp FROM 'F:\kk1.txt'
WITH(
  FIELDTERMINATOR='|',
  ROWTERMINATOR='\n'
)


--默认列分隔符为: 竖线(|)
EXEC master..xp_cmdshell 'bcp test..temp in F:\kk.txt -c -Slocalhost -Usa -Psa'

--  SELECT * FROM temp



--【导出TXT】默认列分隔符为: 竖线(|)
EXEC master..xp_cmdshell 'bcp test..tb out F:\kk.txt -c -Slocalhost -Usa -Psa'
EXEC master..xp_cmdshell 'bcp "Select 姓名,sum(分数) 分数 from test..tb group by 姓名" queryout F:\kk.txt -c -Slocalhost -Usa -Psa'

--必须放在同一行(指定列分隔符和行分隔符)
EXEC master..xp_cmdshell 'bcp "select * from sys.objects o,sys.columns c" queryout F:\bulkTest.txt -c -t"|" -r "\n" -Slocalhost -Usa -Psa'  

用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]       [-e 错误文件]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]             [-U 用户名]           [-P 密码]
  [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
  [-k 保留 Null 值]           [-E 保留标识值]
  [-h"加载提示"]              [-x 生成 xml 格式化文件]
  [-d 数据库名称]
  
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

exec sp_configure 'Ad Hoc Distributed Queries',0 
reconfigure;
exec sp_configure 'xp_cmdshell',0
reconfigure;
exec sp_configure 'show advanced options',0 --关闭高级配置选项设置
reconfigure;