SQL 如何以CSV格式发送查询结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15209620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to send a query result in CSV format?
提问by ETLUser
I am working on ETL and I have this below sql code in my SQL Task in SSIS package. This is how i have coded. I am selecting a data from a table and result of that query as file. I want this attachment sent in CSV format. How do i do it?
我正在研究 ETL，我在 SSIS 包中的 SQL 任务中有以下 sql 代码。这就是我编码的方式。我正在从表中选择数据，并将该查询的结果作为文件。我希望以 CSV 格式发送此附件。我该怎么做？
EXEC sp_send_dbmail @profile_name='default',
@recipients='[email protected]',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file=1
Any help will be very appreciated. Thanks in advance.
任何帮助将不胜感激。提前致谢。
回答by JodyT
Adding @query_result_separatorshould do the trick.
添加@query_result_separator应该可以解决问题。
EXEC sp_send_dbmail @profile_name='default',
@recipients='[email protected]',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','
Adding @query_result_no_padding = 1might clean up the results a bit. All off the arguments can be found here
添加@query_result_no_padding = 1可能会稍微清理结果。所有的论点都可以在这里找到
回答by ossie
@query='
SET NOCOUNT ON;
select ''sep=;''
select ''Col1'',''Col2'',''Col3'',''Col3''
select CONVERT(NVARCHAR,Col1),ISNULL(Col2, ''''),Col4
FROM ...
SET NOCOUNT OFF;
',
--Additional settings
@query_attachment_filename = '*.csv',
@query_result_separator = ';',
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@exclude_query_output = 1,
@append_query_error = 0,
@query_result_header = 0;
回答by rehansheik
回答by Michael Armes
This comment on purple frogindicates you can also use the tab character as a delimiter:
这条关于紫色青蛙的评论表明您还可以使用制表符作为分隔符：
DECLARE @tab char(1) = CHAR(9) EXEC msdb.dbo.sp_send_dbmail @profile_name='donotreply' ,@recipients ='xx@x' ,@query= @query ,@subject= 'xx' ,@attach_query_result_as_file=1 ,@query_attachment_filename='xx.csv' ,@query_result_separator=@tab ,@query_result_no_padding=1 –trim ,@query_result_width=32767 –stop wordwrap
DECLARE @tab char(1) = CHAR(9) EXEC msdb.dbo.sp_send_dbmail @profile_name='donotreply' ,@recipients ='xx@x' ,@query= @query ,@subject= 'xx' ,@attach_query_result_as_file=1 ,@query_attachment_filename='xx.csv' ,@query_result_separator=@tab ,@query_result_no_padding=1 –trim ,@query_result_width=32767 –stop wordwrap
Also looks like this answer's been posted already, my bad: https://stackoverflow.com/a/44315682/5758637
看起来这个答案已经发布了，我的错：https: //stackoverflow.com/a/44315682/5758637
回答by Will
Inside a proc:
在一个过程中：
SELECT
table.myColumn AS [sep=,
myColumn]
, table.myCol2
, table.myCol3...
with a normal hard return within the column alias after "sep=,".
在“sep=,”之后的列别名内有一个正常的硬回车。
