CODEWITHSUNDEEP
sqlssis
ETLUser
ETLUser

Reputation: 401

How to send a query result in CSV format?

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.

Upvotes: 23

Views: 78301

Answers (5)

JodyT
JodyT

Reputation: 4412

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

Upvotes: 43

ossie
ossie

Reputation: 141

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

Upvotes: 14

Will S
Will S

Reputation: 439

Inside a proc:

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

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

Upvotes: 0

Michael Armes
Michael Armes

Reputation: 1096

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

Upvotes: 4

rehansheik
rehansheik

Reputation: 71

Adding 

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

with result solved the issue

See Source

Upvotes: 7

Related Questions