【997755.com澳门葡京】运用脚本输出excel文件的法子共享,Server数据导入导出工具BCP详解

转自:

SQL Server的导出导入情势有:

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


  1. 在SQL Server中提供了导入导出的分界面操作。
  2. 在分界面操作中又分【复制二个或三个表或视图的数量】和【编写查询以钦定要传输的数额】二种方式,第意气风发种是一贯对表、视图实行一切字段、记录实行导出,而第两种就是足以由此SQL语句来支配导出导入的字段和行。
  3. 使用 轻巧但管用的SQL脚本 中的【表复制】这里面包车型客车法子。
  4. 再生机勃勃种正是在命令行中使用bcp命令来导入导出多少,须求非常表明的是,那是对天意据量导入导出就好的方式。

    –整个表导出(out)
    bcp 数据库名.dbo.表名 out
    c:\997755.com澳门葡京,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
    -b5000 -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选项来筛选导入数据的记录行。
    【997755.com澳门葡京】运用脚本输出excel文件的法子共享,Server数据导入导出工具BCP详解。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导入导出多少。

997755.com澳门葡京 1

  1. bcp的主要参数介绍

摘要:

在应用命令xp_cmdshell的时候供给安装权限:

bcp共有多少个动作可以筛选。

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

997755.com澳门葡京 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相关权力需展开
*

/*MSsql2006 如何启用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命令完结,后边跟供给导出的文书名。

997755.com澳门葡京 3

(3) 使用SQL语句导出。

997755.com澳门葡京 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 AUTHORITY\NETWORK 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 版本]                [-Lacrosse允许行使区域安装]
  [-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默许实例。假诺要一连某台机械上的暗许实例,只必要钦点机器名就能够。

-U login_id

钦赐连接SQL Sever的顾客名。

-P password

点名连接SQL Server的顾客名密码。

-T

点名bcp使用信赖连接登录SQL Server。假设未钦定-T,必得内定-U和-P。

-k

钦赐空驶列车使用null值插入,并非那列的暗中同意值。

  1. 哪些运用bcp导出多少

(1) 使用bcp导出任何表或视图。

bcp AdventureWorks.sales.currency out c:/currency1.txt -c -U”sa”
-P”password” –使用密码连接或bcp AdventureWorks.sales.currency out
c:/currency1.txt -c -T –运用信任连接

上边是上述命令实施后的输出结果

Starting copy…

105 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

上边是currency1.txt的片段剧情

AED Emirati Dirham 1998-06-01 00:00:00.000

AFA Afghani 1998-06-01 00:00:00.000

… … …

… … …

ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000

在运用密码登入时须求将-U后的顾客名和-P后的密码加上双引号。

注:bcp除了能够在调控台试行外,还足以透过调用SQL
Server的叁个种类存款和储蓄过程xp_cmdshell以SQL语句的措施运转bcp。如上述第一条命令可改写为

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency out
c:/currency1.txt -c -U”sa” -P”password”‘

执行xp_cmdshell后,重回新闻以表的花样出口。为了能够方便地在SQL中执行bcp,上边包车型客车一声令下都利用xp_cmdshell执行bcp命令。

(2) 对要导出的表举行过滤。

bcp不仅可以够承当表名或视图名做为参数,也得以选择SQL做为参数。通过SQL语句可以对要导出的表进行过滤,然后导出过滤后的记录。

EXEC master..xp_cmdshell ‘bcp “SELECT TOP 20 * FROM
AdventureWorks.sales.currency” queryout c:/currency2.txt -c -U”sa”
-P”password”‘

bcp仍可以透过轻易地设置选项对导出的行开展限定。

那条命令使用了五个参数-F 10和-L 13,表示从SE

EXEC master..xp_cmdshell ‘bcp “SELECT TOP 20 * FROM
AdventureWorks.sales.currency” queryout c:/currency2.txt -F 10 -L 13 -c
-U”sa” -P”password”‘

LECT TOP 20 * FROM
AdventureWorks.sales.currency所查出来的结果中取第10条到13条记下进行导出。

  1. 什么接纳bcp导出格式文件

bcp不仅能够依据表、视图导入导出多少,仍能宽容格式文件对导入导出数据进行节制。格式文件以纯文本文件情势存在,分为日常格式和xml格式。客商能够手工业编写制定格式文件,也足以由此bcp命令依照表、视图自动生成格式文件。

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency format nul
-f c:/currency_format1.fmt -c -T’

上述命令将currency表的构造转换了二个格式文件currency_format1.fmt,上面是那一个格式文件的剧情。

9.0

3

1 SQLCHAR 0 6 “/t” 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 “/t” 2 Name SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 24 “/r/n” 3 ModifiedDate

那么些格式文件记录了这一个表的字段(共3个字段)类型、长度、字符和行分割符和字段名等消息。

bcp还能通过-x选项生成xml格式的格式文件。

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency format nul
-f c:/currency_format2.fmt -x -c -T’

xml格式文件所陈诉的剧情和平常格式文件所陈诉的从头到尾的经过完全后生可畏致,只是格式差别。

  1. 怎么运用bcp导入数据

bcp能够通过in命令将方面所导出的currency1.txt和currency2.txt再重复导入到数据库中,由于currency有主键,由此大家将复制多个和currency的构造完全等同的表。

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM
AdventureWorks.sales.currency

将数据导入到currency1表中

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:/currency1.txt -c -T’

导入数据也同等能够应用-F和-L选项来抉择导入数据的记录行。

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:/currency1.txt -c -F 10 -L 13 -T’

在导入数据时方可依附现已存在的格式文件将满足条件的记录导入到数据库中,不满足则不导入。如上述的格式文件中的第八个字段的字符长度是24,要是有些文本文件中的相应字段的长短抢先24,则那条记下将不被导入到数据库中,其余满足条件的记录健康导入。

利用普通的格式文件

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:/currency1.txt -F 10 -L 13 -c -f c:/currency_format1.fmt -T’

动用xml格式的格式文件

EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:/currency1.txt -F 10 -L 13 -c -x -f c:/currency_format2.fmt -T’

总结

bcp命令是SQL
Server提供的三个快捷的数据导入导出工具。使用它没有须要运行任何图形管理工科具就能够以相当的慢的主意导入导出数据。当然,它也能够透过xp_cmdshell在SQL语句中执行,通过这种艺术能够将其内置客户端程序中(如delphi、c#等)运转,那也是使顾客端程序具备数据导入导出功能的法子之大器晚成。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website