Export sql query to CSV file

Question

Export sql query to CSV file

ilugopal

SSC Veteran

Points: 212
More actions
February 22, 2013 at 3:51 am

#274398

While I'm exporting sql query details to CSV file I get the column headers and after the column I get minus symbol below the column headers followed by the data. IS there a way where I can get only the column headers and not the minus symbol.
Server_Name Instance_name User_name
------------- -------------- ----------
xys.com sql24 user1
xys.com sql24 user1
xys.com sql24 user1
I dont want this "---------" symbol. I want the o/p as
Server_Name Instance_name User_name
xys.com sql24 user1
xys.com sql24 user1
xys.com sql24 user1

Viewing 4 posts - 1 through 3 (of 3 total)

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Two options in my opinion:-

1 Run the query to grid then on the grid Right click on grid -> Select all -> Right click on grid ->copy with headers -> paste into excel save as CSV.

2 If you want to run this on a scheduled basis use SSIS to export the data as csv using a query along the lines of select Server_Name, Instance_name, User_name union all select col1, col2, col3 from table"

-------------------------------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

Robert Klimes SSCoach Points: 17865 More actions · Answer 2

you could use BCP as well.

BCP "select col1+','+col2+','+col3 from dbo.table" queryout c:\temp\file.csv -c -T

or if you have xp_cmdshell enabled you can run it from SSMS or sql agent(note: xp_cmdshell can be a security risk).

exec xp_cmdshell ''bcp "select col1+','+col2+','+col3 from dbo.table" queryout c:\temp\file.csv -c -T''

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 3

ilugopal (2/22/2013)
While I'm exporting sql query details to CSV file I get the column headers and after the column I get minus symbol below the column headers followed by the data. IS there a way where I can get only the column headers and not the minus symbol.
Server_Name Instance_name User_name
------------- -------------- ----------
xys.com sql24 user1
xys.com sql24 user1
xys.com sql24 user1
I dont want this "---------" symbol. I want the o/p as
Server_Name Instance_name User_name
xys.com sql24 user1
xys.com sql24 user1
xys.com sql24 user1

How are you doing the export? For example, are you using SQLCmd, BCP, or someother method? I ask because it does make a real difference.

--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)

Export sql query to CSV file

Cookies on SQLServerCentral