CSV file splitting my row data into separate rows

Question

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!

Emailing the file as an attachment, or are you putting the content in the email body? — Tony Hopkinson
– Tony Hopkinson, Commented Jan 12, 2015 at 16:38
Good! Or bad seeing as that's the easy potential source of such an error. Thing you have to find out now is where is it going wrong. e.g. if you just create the file are there any extra end of line delimiters appearing in there. If not, if you import the file as the recipient is doing do you get the issue, etc. Could be any number of problems this. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 12, 2015 at 16:46
the user won't be importing the file! they just want to open it in excel and see the data! If I run the query in SQL Management Studio and then right click to export the data to CSV then the data is correct! — user3538102
– user3538102, Commented Jan 12, 2015 at 16:48
for excel to interpert a new line, a new line feed and new line must have been used. Open the CSV in a notepad. Do you see multiple lines for the same record? if so then the utility generating the CSV is somehow adding vbcrlf or line feed and return to the record. — xQbert
– xQbert, Commented Jan 12, 2015 at 16:50

Bacon Bits · Accepted Answer · 2015-01-12 17:26:39Z

3

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 = ','?

edited Jan 12, 2015 at 17:26

answered Jan 12, 2015 at 17:17

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

CSV file splitting my row data into separate rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related