Remove ---- from output file

Question

Post reply

Remove ---- from output file

mssqlsrv

SSCrazy

Points: 2375
More actions
November 17, 2014 at 11:59 pm

#292193

Hi Team,
I am sending one report using db_mail in sql server 2008 R2.
My problem is
1. The CSV file is having ------------------- in output below columns.
Is there any way to remove '--------' from output file.
2. CSV file have ',' as column separator.
While opening with excel I guess it should automatically open in separate columns(not sure).
Please help.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mssqlsrv SSCrazy Points: 2375 More actions · Answer 1

mssqlsrv

SSCrazy

Points: 2375

November 19, 2014 at 10:45 pm

#1759714

Someone pls help.

Stuart Davies SSCoach Points: 19505 More actions · Answer 2

What code are you using to create the csv?

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

The '-------' below the column names is the seperator between the header and the data. You will have the same if you show your query results in text mode. If you use your own code to generate the CSV file, you should modify it by first creating the file with only the headers and next only add the data (without headers). If you use the 'save results to file' option from SSMS (or something alike) I don't think you can get it without the '------'. The remaining option is to remove it afterward with some external coding (powershell?).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Lowell SSC Guru Points: 323504 More actions · Answer 4

i've seen this before, i think this is part of sp_send_dbmail when you use the @attach_query_result_as_file = 1 option alongside @query_result_header = 1.

as i remember it, you have to NOT use headers, and use a UNION statment to join headers manually plus the data;

select 'Column1','Column2' UNION ALL

select RealData1RealData2 from sometable

i think that was one of the motivating factors for why i use CLR a LOT for exports.

between more control, and needing to create ANSI vs UTF8 file formats, i moved away from file-as attachment a while ago.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RTaylor2208 SSChampion Points: 13298 More actions · Answer 5

As has already been asked how are you generating your csv file? This dictates what can or cannot be done to remove the headers and dashes.

For example running a query using sqlcmd with the switch -h -1 will remove these from the output if writing the csv this way.

MCITP SQL 2005, MCSA SQL 2012

mssqlsrv SSCrazy Points: 2375 More actions · Answer 6

Hi All,

I am using below query to send emails.

declare @sql as varchar(4000)

set @sql =' SET NOCOUNT ON

SELECT columns

FROM tables with joins'

EXEC msdb.dbo.sp_send_dbmail

@body_format = 'HTML',

@query = @sql,

@attach_query_result_as_file = 1,

@query_result_separator =',',

@query_result_width = 32767,

@query_attachment_filename = @filename

RTaylor2208 SSChampion Points: 13298 More actions · Answer 7

Try setting this parameter to 0:

From msdn:

[ @query_result_header= ] query_result_header

Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

MCITP SQL 2005, MCSA SQL 2012

mssqlsrv SSCrazy Points: 2375 More actions · Answer 8

Tried setting parameter zero for @query_result_header.

Its removing ---- with headers. I need headers.

RTaylor2208 SSChampion Points: 13298 More actions · Answer 9

One other way to do it then is to have the headers turned off and have the first first row of your select the column names, something like:

SELECT 'COLUMN1', 'COLUMN2',....

FROM Blah UNION

SELECT COLUMN1, COLUMN2

FROM Blah

MCITP SQL 2005, MCSA SQL 2012