How to send a query result in CSV format?

Question

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?

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.

If you are using SSIS then why not use the features of SSIS to create a CSV file and the Send Mail Task to send the email? — JodyT
– JodyT, Commented Mar 4, 2013 at 19:37
I added an answer below that should format the query result as a CSV and added some more information about the sp_send_dbmail SP. Could you accept the answer if this worked for you? — JodyT
– JodyT, Commented Mar 5, 2013 at 8:22

End genocide - save Gaza · Accepted Answer · 2020-03-17 16:07:04Z

43

Adding @query_result_separator should do the trick.

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 = 1 might clean up the results a bit. All off the arguments can be found here

edited Mar 17, 2020 at 16:07

End genocide - save Gaza

25k10 gold badges113 silver badges133 bronze badges

answered Mar 4, 2013 at 20:15

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dev_etter Over a year ago

The line with @attach_query_result_as_file=1 needs a comma at the end, otherwise the code throws an error.

Chris Rogers Over a year ago

Please note that these results will not be properly CSV formatted (escaped). If any of your fields contain data with commas, you're in trouble

gnaanaa Over a year ago

This answer does not work with opening CSV file in MS Excel. The separator is not enough, but need to specify the separator in the first line in the CSV file as sep=,. Otherwise all the columns will show in one column in MS Excel.

youcantryreachingme Over a year ago

FWIW, I found if your data contains commas then if you set @temp = char(9); and use the sp_send_dbmail parameter: @query_result_separater = @temp; you will have a tab-delimited file. Save it with the '.csv' extension regardless, and Excel will open the file on double-click (rather than having to open Excel then open or import the file and map fields to columns or specify the delimiter).

End genocide - save Gaza · Accepted Answer · 2020-03-17 16:06:31Z

14

@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;

edited Mar 17, 2020 at 16:06

End genocide - save Gaza

25k10 gold badges113 silver badges133 bronze badges

answered Jun 25, 2014 at 12:40

ossie

1411 silver badge2 bronze badges

2 Comments

smoore4 Over a year ago

Good idea about the NOCOUNT in the @query definition. +1

Will S Over a year ago

Inside a proc: SELECT table.myColumn AS [sep=, myColumn] with a normal hard return within the column alias.

wmk · Accepted Answer · 2015-11-27 19:00:24Z

7

Adding

'[sep=,' + CHAR(13) + CHAR(10) ColumnName]

with result solved the issue

See Source

edited Nov 27, 2015 at 19:00

wmk

4,6241 gold badge23 silver badges38 bronze badges

answered Nov 27, 2015 at 15:00

rehansheik

711 silver badge2 bronze badges

Comments

End genocide - save Gaza · Accepted Answer · 2020-03-17 16:05:01Z

4

This comment on purple frog indicates 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

Also looks like this answer's been posted already, my bad: https://stackoverflow.com/a/44315682/5758637

edited Mar 17, 2020 at 16:05

End genocide - save Gaza

25k10 gold badges113 silver badges133 bronze badges

answered Oct 6, 2017 at 18:32

Michael Armes

1,1122 gold badges17 silver badges32 bronze badges

Comments

End genocide - save Gaza · Accepted Answer · 2020-03-17 16:05:25Z

0

Inside a proc:

SELECT 
table.myColumn AS [sep=, 
myColumn]
, table.myCol2
, table.myCol3...

with a normal hard return within the column alias after "sep=,".

edited Mar 17, 2020 at 16:05

End genocide - save Gaza

25k10 gold badges113 silver badges133 bronze badges

answered Nov 20, 2018 at 16:44

Will S

4494 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to send a query result in CSV format?

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related