- 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/,分别为 导出/导入/混合模式,
- IMEX参考:http://bbs.csdn.net/topics/120096529
- */
- 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;
SQLServer excel和txt的导入导出
最新推荐文章于 2024-08-12 09:45:14 发布