Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Export sql query to CSV file Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 3:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:22 AM
Points: 4, Visits: 8
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
Post #1422965
Posted Friday, February 22, 2013 4:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 3,002, Visits: 3,179
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
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
Post #1422974
Posted Friday, February 22, 2013 7:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 1,343, Visits: 2,518
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
Post #1423075
Posted Friday, February 22, 2013 4:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse