CODEWITHSUNDEEP
sqlsql-server-2008
user3538102
user3538102

Reputation: 171

CSV file splitting my row data into separate rows

I have a a query that creates a CSV file and then emails it out to a user but the CSV file is not in the correct format. For some reason when the CSV file is opened it is separating some of my rows into 2 separate rows for some unknown reason! The spliting of the rows seems to be getting done at random because some rows are split while other rows remain intact. This is a difficult scenario to explain so below shows the query I have wrote for creating the CSV file and emailing it out to the user and then the results of the CSV file!

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='TestProfile',
@recipients='[email protected]',
@subject='Test message',
@body='This is a test.',
@query = 'Select *
FROM (
SELECT 
    replace(LEFT(convert(NVARCHAR, getdate(), 106),6) + ''-'' + RIGHT(year(convert(NVARCHAR, getdate(), 106)),2), '' '', ''-'') as [Date],
    FirstName, Surname,TestTime as TestTime ,  Percentage as Percentage, Score as Score       
FROM TestDatabase.dbo.TestingTable
) as s
PIVOT
(
SUM(Score)
FOR [TestTime] IN ([00:00],[00:30],[01:00],[01:30],[02:00],[02:30],[03:00],[03:30],[04:00],[04:30],[05:00],[05:30],[06:00],[06:30],[07:00],[07:30],[08:00],[08:30],[09:00],[09:30],[10:00],[10:30],[11:00],[11:30],[12:00],[12:30],[13:00],[13:30],[14:00],[14:30],[15:00],[15:30],[16:00],[16:30],[17:00],[17:30],[18:00],[18:30],[19:00],[19:30],[20:00],[20:30],[21:00],[21:30],[22:00],[22:30],[23:00],[23:30])
) Results
',
@query_result_separator = ' ',
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1, 
@query_attachment_filename = 'test.csv',
@query_result_no_padding = 1

Output

enter image description here

When in fact it should look like this:

enter image description here

Bottom image is the correct format and the top row goes from 00.00 to 23.30 as intended!

Upvotes: 1

Views: 2251

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

By default, sp_send_dbmail inserts a line break after 256 characters on any given line. You can modify this by specifying the @query_result_width parameter when calling the stored procedure. Maximum value is 32767.

Aside: If this is supposed to be a CSV file, why is @query_result_separator = ' ' and not @query_result_separator = ','?

Upvotes: 3

Related Questions