Export sql query to CSV file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Export sql query to CSV file

Export sql query to CSV file

Rate Topic

Display Mode

Topic Options

Author

Message

ilugopal

Posted Friday, February 22, 2013 3:51 AM

Forum 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

Stuart Davies

Posted Friday, February 22, 2013 4:16 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:19 AM
Points: 2,473, Visits: 2,131

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

Robert klimes

Posted Friday, February 22, 2013 7:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 888, Visits: 1,848

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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1423075

Jeff Moden

Posted Friday, February 22, 2013 4:01 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

ilugopal (2/22/2013)

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1423286

« Prev Topic | Next Topic »

Permissions