将查询结果导出到CSV文件并标准化数据格式

　　在成产环境中，经常会有各种出report的需求，可以在数据库中将需要的信息查询出来，然后生成csv文件，定期使用邮件发出

　　本例将讲解两种将查询结果导出到Excel的方法：

　　1.SQLCMD

　　使用SQLCMD简单方便：

　　关于SQLCMD的参数介绍，可以参考官方文档：

　　https://docs.microsoft.com/zh-cn/sql/tools/sqlcmd-utility?view=sql-server-2017

　　在CMD中执行以下语句即可：

　　CMD:sqlcmd -S VWBJVWTSQLT1165 -E -Q "select * from [Score_Analysis].[dbo].[Temp_XPF] order by id desc" -o"D:\BI_Test\1.csv" -s"," -W

　　-E是信任连接，使用该参数就不用输入账号密码， -Q是执行查询并退出， -s是分隔符，-s","是指将查询结果按照","来分隔，这样才会在Excel中显示为分列的结果，

-W是指在结果中将字符后面的空格都删掉，-o是指输出到文件，本例将查询结果输出到csv文件。

　　如果查询语句比较复杂，可以将查询语句保存在.sql文件中，比如test.sql，然后将-Q参数替换成-i，输入该文件：

　　-Q "select * from [Score_Analysis].[dbo].[Temp_XPF] order by id desc"——> -i "test.sql"

　　

　　2.BCP命令在SSMS中实现：

　　1>.在实例中打开xp_cmdshell:

  EXEC sp_configure 'show advanced options', 1; 

  GO 

  EXEC sp_configure 'xp_cmdshell', 1; 

  GO 

  RECONFIGURE; 

  GO 

 

　　2>.BCP查询出的结果不带表头，所以需要将表头插入到表的数据中，故此先创建一个存数据的表，将需要查询的数据放入该表中，将表头信息插入该表，然后以后每次都删除除了表头信息行的其它数据，然后进行插入：

　　比如要查询的信息是：select id,tenantid,name,displayname,CreationTime from [JustMeeting].[dbo].[AbpRoles] 

　　

 

 　　此时建立一个新表用来存放要查询的信息：

Create table [Score_Analysis].[dbo].[Temp_XPF]

(

id varchar(100),

tenantid varchar(100),

name varchar(100),

displayname varchar(100),

CreationTime varchar(100),

)　　

　　将表头信息先插入该表：

insert into [Score_Analysis].[dbo].[Temp_XPF] values('id','tenantid','name','displayname','CreationTime')

　　以上步骤只执行一次，在之后的工作中，可以用Job重复执行后面的步骤，就可以每次查询最新的数据，并导出到按时间格式命名的csv文件中。

　　

　　3>此步骤和步骤4可以放入Job中定期执行

　　每次查询最新数据前删除掉旧数据，但是保留表头：

delete from [Score_Analysis].[dbo].[Temp_XPF] where id <> 'id'

　　然后插入新数据：

insert into [Score_Analysis].[dbo].[Temp_XPF] select convert(varchar(100),id) as id, convert(varchar(100),tenantid) as tenantid, convert(varchar(100),name) as name,

 convert(varchar(100),displayname) as displayname, convert(varchar(100),CreationTime) as CreationTime  from [JustMeeting].[dbo].[AbpRoles]  where id>3

　　

　　4>.使用动态语句，定义文件名字以日期命名，拼接处BCP命令，导出csv文件：

declare @sql varchar(1000),@date varchar(100)

declare @filepath varchar(100)

set @date=replace(convert(varchar,getdate(),23),'-','')　　--定义日期格式为20190101格式

set @filepath='D:\BI_Test\DPMReport_'+@date+'.csv'　　--定义文件名使用日期表示的DPMReport_20190101.csv

set @sql='exec master..xp_cmdshell '+ '''bcp "select * from [Score_Analysis].[dbo].[Temp_XPF] order by CreationTime" queryout '+@filepath+' -c -t "," -T'''

--print(@sql)

exec (@sql)

 

bcp命令最后由动态语句生成为：exec master..xp_cmdshell 'bcp "select * from [Score_Analysis].[dbo].[Temp_XPF] order by CreationTime" queryout D:\BI_Test\DPMReport_20190916.csv -c -t "," -E -T'
bcp参数可以参考https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-2017
-T是指可信任连接，-c是指指定字符数据类型来执行该操作，-t指定字段终止符，在此用逗号将字段分开放入csv的每列中

 

欢迎留言讨论~