Create CSV file using sp_send_dbmail with field delimiters
-
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.
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Awesome Jeff, thank you very much, the results are just what I wanted!
Steve
-
Cool... thanks for the feedback, Steve.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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!
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply