CODEWITHSUNDEEP
sqlsql-server-2008
user3538102
user3538102

Reputation: 171

set delimiter when using sp_send_dbmail (csv file)

I have a query that brings me back a result set but when I use sp_send_dbmailto send an email to someone containing this result set as a CSV file it opens in excel in the incorrect format! I know that I can correct this format through excel but I don't want the user to do that! I want them to just be able to open the file and everything be visible in the correct format. Below shows how I am creating the CSV file and emailing it out the someone (I am also specifying the seperator but it doesn't work and I can't figure out why):

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='TestProfile',
@recipients='[email protected]',
@subject='Test message',
@body='This is a test.',

@query = 'Select firstName, LastName, Address, Score from TestData.dbo.Student',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_result_separator = ',',
@query_result_width = 25,
@query_attachment_filename = 'Test.csv',
@query_result_no_padding = 1

Once the CSV File is received and opened all the data is represented in the first column, which isn't the desired results!

enter image description here

Screenshot of my list seperator settings

enter image description here

Upvotes: 2

Views: 24362

Answers (4)

Christian
Christian

Reputation: 389

If you are using Excel anyway, you can override Excel list separator and remove the problem all together, across every single regional setting out there.

Add the following line in the beginning of the CSV file: sep=;

In your example you would have this inside your @query.

..
@query_result_separator = ';',
@query = '
print ''sep=;''
SELECT 1,2'
..

Content of csv:

sep=;
1;2

Result; correct separation. Always!

Column Separation in Excel, using forced separator

Upvotes: 3

Manoj Das
Manoj Das

Reputation: 101

I struggled with this issue for a couple of days , but finally got it to work

@query_result_separator =' ', did the trick, it's TAB as the result separator.

Full code EXEC msdb.dbo.sp_send_dbmail

              @profile_name ='MailProfile',
              @from_address = '[email protected]',
              @recipients = '[email protected]',
              @body = @varBody,
              @body_format = 'HTML',
              @execute_query_database ='MyDB',
              @query = @VarSQL,
              @attach_query_result_as_file = 1,
              @query_result_separator ='    ',
              @exclude_query_output =1,
              @query_result_no_padding=1,
              @query_result_header =1,
              @query_attachment_filename ='MyDB.csv'

Upvotes: 7

DrCopyPaste
DrCopyPaste

Reputation: 4117

I think I could reproduce your issue with the data you provided. I found the answer in this superuser post.

It seems, Excel ignores your list separator if it is the same character as another already used character (for example decimal separator), it will work when you switch decimal separator to something else. But since you need this working on other client machines, too, you should consider switching to another csv-separator altogether (;?)

Upvotes: 0

Andrei Bucurei
Andrei Bucurei

Reputation: 2458

You can't count on user regional settings or the application used to open the file.

Besides, any Excel user should know about Data->Text To Columns function.

The only thing you should take care of are fields that might contain the field separator (,) like addresses which should be wrapped with text qualifiers (e.g. ").

Upvotes: 0

Related Questions