mssql sqlserver 使用脚本输出excel文件的方法分享

作者:计算机专家

转自:

SQL Server的导出导入情势有:

 SQL Server数据导入导出工具BCP详解


  1. 在SQL Server中提供了导入导出的分界面操作。
  2. 在分界面操作中又分【复制三个或多个表或视图的数目】和【编写查询以钦点要传输的数额】二种形式,第一种是直接对表、视图进行全方位字段、记录实行导出,而第三种就是能够通过SQL语句来支配导出导入的字段和行。
  3. 使用 简单但管用的SQL脚本 中的【表复制】那中间的章程。
  4. 再一种正是在指令行中使用bcp命令来导入导出多少,须要特意表明的是,那是对时局据量导入导出就好的法子。

    --整个表导出(out)
    bcp 数据库名.dbo.表名 out c:currency.txt -S"数据库实例" -U"顾客" -P"密码" -c 

    --使用SQL语句导出(queryout)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"客商" -P"密码" -c

    --设置字段分隔符和行分隔符(-c -t"," -r"n"),不想输入字段类型等请合作-c一同使用
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"顾客" -P"密码" -c -t"," -r"n"

    --内定每批导入数据的行数、钦点服务器发出或收受的各类网络数据包的字节数(-k -b伍仟 -a65535)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"客商" -P"密码" -c -t"," -r"n" -k -b5000-a65535

    --在询问分析器上施行(EXEC master..xp_cmdshell)
    EXEC master..xp_cmdshell 'bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"顾客" -P"密码" -c'

    --把SQL语句生成三个.sql文件,然后调用
    --注:路线的文件夹名称中间不能够有空格
    exec master..xp_cmdshell 'osql -S 数据库实例 -U 客户 -P 密码 -i    C:cmdshellTest.sql'  

    --将数据导入到currency表中
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -T'
    --导入数据也同样能够利用-F和-L选项来抉择导入数据的记录行。
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -F 10 -L 13 -T'

bcp是SQL Server中承受导入导出数据的三个命令行工具,它是基于DB-Library的,何况能以相互的章程快捷地导入导出大量的数据。bcp能够将数据库的表或视图直接导出,也能透过SELECT FROM语句对表或视图进行过滤后导出。在导入导出数据时,能够选用暗中同意值或是使用壹个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。上面将详细评论哪边使用bcp导入导出多少。

图片 1

  1. bcp的主要参数介绍

摘要:

在使用命令xp_cmdshell的时候必要安装权限:

bcp共有五个动作能够选用。

下文将享用应用sql脚本输出excel的点子
 
此脚本能够利用于 表或视图生成excel的章程,
若需使用sql脚本输出excel数据,我们可将sql脚本生成视图或一时表,

图片 2

(1) 导入。

下一场再输出excel

输出excel语法简要介绍:
exec
master..xp_cmdshell 'bcp [数据库名称].[架构名].[表名] out [excel寄放地方全路径] -c -q -S"服务器Ip" -U "sql用户名" -P "sql密码"'

---例1:
exec 
master..xp_cmdshell 'bcp test.dbo.tableName out d:test.xls -c -q -S"." -U "sa" -P "erp"'
---将数据库test中tablName输出值d盘test.xls文件
---服务器地址.
---sql账户sa
---sql密码erp
--------------------------------------------
例2: 根据动态文件名输出excel的方法
declare @name varchar(30) ---动态文件名
set @name ='d:test123.xlsx'

exec 
('master..xp_cmdshell ''bcp test.dbo.tableName out '+@name+' -c -q -S"." -U "sa" -P "erp"''') ---拼接操作语句,并采用exec执行

注意事项:

*1 使用脚本输出的excel ,无表头
2 可动态文件名和动态sa账户密码生成相关音讯
3 假如出口sql脚本的多寡至excel,我们需先将sql脚本生成的数据缓存至表中,然后输出表数据至excel

xp_cmdshell相关权力需展开
*

/*MSsql二〇〇五 如何启用xp_cmdshell
暗中同意境况下,sql server二〇〇七安装完后,xp_cmdshell是禁止使用的(或者是高枕而卧着想),借使要使用它,可按以下步骤
*/
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

以此动作使用in命令完毕,后边跟要求导入的公文名。

--施行想要的xp_cmdshell语句
Exec xp_cmdshell 'query user'
GO

(2) 导出。

--用完后,要记得将xp_cmdshell禁止使用(出于安全思量)
-- 允许配置高等选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--重新配置
RECONFIGURE
GO

那几个动作使用out命令完结,后边跟要求导出的文书名。

图片 3

(3) 使用SQL语句导出。

图片 4

以此动作使用queryout命令实现,它跟out类似,只是数据源不是表或视图名,而是SQL语句。

 

(4) 导出格式文件。

 

这一个动作使用format命令完结,后而跟格式文件名。

 

上面介绍部分常用的选项:

 

-f format_file

遇到的失实:

format_file表示格式文件名。那么些选项重视于上述的动作,假使利用的是in或out,format_file表示早就存在的格式文件,若是使用的是format则表示是要调换的格式文件。

1、产生以下错误:
[Error][Microsoft][Native]Error = [Microsoft][SQL Native Client]没辙开垦 BCP 主数据文件

-x

使用如下命令:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%%USERNAME%'
返回 :NT AUTHORITYNETWORK SERVICE

以此选项要和-f format_file合作使用,以便生成xml格式的格式文件。

然后在布署管理器(configuration manager)里面包车型大巴SQL server二〇〇六服务里张开,看见登录内置账号为Network service,
改成local system问题消除。

-F first_row

2、SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]对于造型表明无效的字符值

点名从被导出表的哪一行导出,或从被导入文本的哪一行导入。

设假使表与表之间的数目导入,可用 -N, 大概 -w, 而不要用 -c
用-c的话, 如果导出的某部列中的数据中含有分隔符, 则会促成你 bcp 导入的时候失败
-N 大概 -w 不会有其一主题素材

-L last_row

 

点名被导出表要导到哪一行结束,或从被导入文本导数据时,导到哪一行甘休。

bcp用法: 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 版本]                [-Wrangler允许使用区域安装]
  [-k 保留空值]                  [-E 保留标记值]
  [-h"加载提醒"]                 [-x 生成 xml 格式化文件]

-c

 

行使char类型做为存款和储蓄类型,未有前缀且以"/t"做为字段分割符,以"/n"做为行分割符。

 

-w

其它:

和-c类似,只是当使用Unicode字符集拷贝数据时选拔,且以nchar做为存款和储蓄类型。

新建查询->输入SQL查询语句,实施获得必要的结果,在询问结果栏点击鼠标右键->将结果另存为 
导出文件(*csv)就可以导出为 逗号分割的excel文件。 
那会儿,由于并未分列,所以应先新建二个空荡荡的Excel文书档案,然后选择展开刚才保存的的文书; 
接下来它会并发文本导入步骤,选中分割符号,下一步,选中 逗号,预览区域健康,然后在下一步和成功,保存为excel文件

-t field_term

钦点字符分割符,暗中认可是"/t"。

-r row_term

点名行分割符,默许是"/n"。

-S server_name[ /instance_name]

钦命要三番五次的SQL Server服务器的实例,假如未钦赐此选项,bcp连接本机的SQL Server暗中认可实例。假使要一连某台机械上的暗中同意实例,只要求钦定机器名就可以。

本文由杏彩发布,转载请注明来源

关键词: