July 21, 2009 at 3:01 pm
I am trying to send an email that creates a CSV file from a query, that also will enclose each column with double-quotes. I am able to generate the CSV file okay. But the data may have commas in it, so I'd like to differentiate those commas by surrounding the column with double-quotes. Here's how I'm calling it:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyMailProfile',
@recipients = 'myaddress@email',
@copy_recipients = null,
@blind_copy_recipients = null,
@subject = 'This is a test of sending CSV file "here is the csv file"',
@body_format = 'TEXT',
@body = 'Body of the test message',
@importance = 'Normal',
@sensitivity = 'Normal',
@file_attachments = null,
@query = 'SET NOCOUNT ON;SELECT * FROM CSV.[MoreInfo];SET NOCOUNT ON;',
@execute_query_database = 'myDatabase',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Myfile.csv',
@query_result_header = 1,
@query_result_width = 32767, -- can go to 32767 for query width
@query_result_separator = ',',
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_no_padding =1 -- turn off padding of fields with spaces
My data looks like this:
Date,FirstName,MiddleInitial,LastName
----,---------,-------------,--------
Today,Bob,J,Schwarz
9/12/09,Bob,J,Schwarz
Today,Bob,xyz,J,abcdef,Schwarz
9/15/09,Bob,J,Schwarz
But I'd like it to look like this:
Date,FirstName,MiddleInitial,LastName
----,---------,-------------,--------
"Today","Bob","J","Schwarz"
"9/12/09","Bob","J","Schwarz"
"Today","Bob,xyz","J","abcdef,Schwarz"
"9/15/09","Bob","J","Schwarz"
I'd like it to be a CSV file so it can be read into Excel.
Thank you in advance.
July 21, 2009 at 5:24 pm
Ironically, the article on how to post data for a thread also contains your answer. Please see the first link in my signature below.
--Jeff Moden
July 22, 2009 at 11:35 am
Jeff,
Thanks for your advice on posting etiquette, this was my first post. It didn't quite work though, now the CSV file that is being created surrounds the values with brackets [] instead of double-quotes. The query that I'm passing to sp_send_dbmail executes correctly in stand-alone mode, generating the double-quotes, but doesn't use those double-quotes in the CSV file. My code, (including table creation and value generation) looks like this, though it's adding a single-quote, not a double-quote, as I haven't figured out how to put in enough quotes to create a double-quote.
--===== If the test table already exists, drop it
IF OBJECT_ID('dbo.mytable','U') IS NOT NULL
DROP TABLE dbo.mytable
/****** Object: Table dbo.mytable */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.mytable(
[Date] [varchar](500) NULL,
[FirstName] [varchar](500) NULL,
[MiddleInitial] [varchar](500) NULL,
[LastName] [varchar](500) NULL,
[Address] [varchar](500) NULL,
[City] [varchar](500) NULL,
[State] [varchar](500) NULL,
[Zip] [varchar](500) NULL,
[Phone] [varchar](500) NULL,
[From] [varchar](500) NULL,
[HighSchool] [varchar](500) NULL,
[YearOfGrad] [varchar](500) NULL,
[Birthdate] [varchar](500) NULL,
[AcademicInterestMajor] [varchar](500) NULL,
[ExtracurricularInterests] [varchar](500) NULL,
[Semester] [varchar](500) NULL,
[InfoRequest] [varchar](500) NULL,
[InfoRequest2] [varchar](500) NULL,
[AdditionalComments] [varchar](500) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [dbo].[mytable]
([Date]
,[FirstName]
,[MiddleInitial]
,[LastName]
,[Address]
,[City]
,[State]
,[Zip]
,[Phone]
,[From]
,[HighSchool]
,[YearOfGrad]
,[Birthdate]
,[AcademicInterestMajor]
,[ExtracurricularInterests]
,[Semester]
,[InfoRequest]
,[InfoRequest2]
,[AdditionalComments])
SELECT 'Today','Alex','J','Johnson','Testing','Milwaukee','WI','53706','618-444-3191','alex.Johnson@uwc.edu','hamilton','2008','03-13-90','Chemical','','Spring','Admissions Information','Transfer Information','Testing this form space'
UNION ALL
SELECT '9/12/09','Alex','J','Johnson','Testing','Milwaukee','WI','53075','(618) 444-8935','alex.Johnson@uwc.edu','hamilton','2008','03-13-90','Chemical','','Spring','General Campus Info','Degree Information','Testing this form space'
UNION ALL
SELECT 'Today','Alex,xyz','J','abcdef,Johnson','Testing','Milwaukee','WI','53706','618-444-3191','alex.Johnson@uwc.edu','hamilton','2008','03-13-90','Chemical','','Spring','Admissions Information','Transfer Information','Testing this form space'
UNION ALL
SELECT '9/15/09','Alex','J','Johnson','Testing','Milwaukee','WI','53075','618-444-3191','alex.Johnson@uwc.edu','Hamilton','2008','03-13-90','Chemical','','Spring','General Campus Info','Degree Information','Testing form space'
select * from dbo.mytable
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'myemail@domain.com',
@copy_recipients = null,
@blind_copy_recipients = null,
@subject = 'This is a test of sending CSV file "here is the csv file"',
@body_format = 'TEXT',
@body = 'blah blah blah test message',
@importance = 'Normal',
@sensitivity = 'Normal',
@file_attachments = null,
@query = 'SET NOCOUNT ON;SELECT QUOTENAME(Date,''''''"''''''),QUOTENAME(FirstName,''''''"''''''),QUOTENAME(MiddleInitial,''''''"''''''),QUOTENAME(LastName,''''''"'''''') FROM dbo.[mytable];SET NOCOUNT ON;', @attach_query_result_as_file = 1,
@query_attachment_filename = 'Somefile.csv',
@query_result_header = 1,
@query_result_width = 32767, -- can go to 32767 for query width
@query_result_separator = ',',
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_no_padding =1 -- turn off padding of fields with spaces
July 22, 2009 at 10:55 pm
Ah.... very cool. Thanks for posting the test data. 🙂
I believe your problem is that you've simply included too many single quotes in QUOTENAME for the task at hand. Ipso facto...
DECLARE @Query VARCHAR(MAX)
--===== Corrected query returns correct result (double quoted)...
SELECT @query = 'SET NOCOUNT ON;SELECT QUOTENAME(Date,''"''),QUOTENAME(FirstName,''"''),QUOTENAME(MiddleInitial,''"''),QUOTENAME(LastName,''"'') FROM dbo.[mytable];SET NOCOUNT ON;'
EXEC (@Query)
--===== Original query returns incorrect result (single quoted)...
SELECT @query = 'SET NOCOUNT ON;SELECT QUOTENAME(Date,''''''"''''''),QUOTENAME(FirstName,''''''"''''''),QUOTENAME(MiddleInitial,''''''"''''''),QUOTENAME(LastName,''''''"'''''') FROM dbo.[mytable];SET NOCOUNT ON;'
EXEC (@Query)
--Jeff Moden
July 23, 2009 at 8:01 am
Awesome Jeff, thank you very much, the results are just what I wanted!
Steve 🙂
July 23, 2009 at 1:05 pm
Cool... thanks for the feedback, Steve.
--Jeff Moden
September 21, 2010 at 12:31 pm
I was able to get something similar working, but it doesn't open in Excel. Well, it opens, but each row of data is just crammed into one cell. Any thoughts as to ways I could get this to be formatted in such a way that when it comes out of SQL Server it's able to be opened in Excel? Seems like a reasonable thing to be able to do.
Thanks!
September 23, 2010 at 8:50 pm
john.hofmann 74371 (9/21/2010)
I was able to get something similar working, but it doesn't open in Excel. Well, it opens, but each row of data is just crammed into one cell. Any thoughts as to ways I could get this to be formatted in such a way that when it comes out of SQL Server it's able to be opened in Excel? Seems like a reasonable thing to be able to do.Thanks!
Make the output tab separated.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply