I need to copy data from SQL app to CSV.

I am helping a new client.  I'm a network guy, not a developer.  The client needs to extract data from a SQL Server app to a CSV file.  I can access the tables and see the data via SQL Query Analyzer, but I am lost at how I can easily extract the data to CSV.

Who is Participating?
MohammedUConnect With a Mentor Commented:
Open Query Analyzer and Set the results to be displayed in Grid format, enter and execute your query, click in the results pane and choose File -> Save As. Here you have the option to save the result as CSV.

If you simply want to export a table in CSV format see the DTS Wizard and BCP in SQL Server Books Online.

rickmillsPresidentAuthor Commented:
I appreciate the input, but as I mentioned, I am not a developer.  Is there any simply way to accomplish this task?  I have about 2 hours to accomplish this task or the data cannot be used.  If there is a tool I can purchase that would make it quick and easy, I would be willing to part with a few dollars.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

HoggZillaConnect With a Mentor Commented:
DTS is the best tool in SQL Server just for something like this.
If it is a onetime run, right click on the table in Query Analyzer, select Open. Select the top left corner and right click, save as. Pick CSV and there you are. Easy.
You don't need to buy any tool...Data transformation Services (DTS) tools comes with SQL server...

Check the following...
rickmillsPresidentAuthor Commented:
When I right-click on the top left corner (blank cell above row 1 and to the left of the first column) and select Save As, I end up with a file containing a "2".  That's it.  "2" is the value the first column in the first row, so I assume that's where its coming from.  

Also, if I select Edit / Select All, it does not highlight all rows in the table.  Only the currently selected row remains highlighted.

Mohammed, I'm going through the DTS tutorial, but if anyone knows of a faster, easier way to accomplish this, I would appreciate it.  I'm under a tight time crunch and am certainly outside of my element.
Thanks guys,
Easier way from memory. Download SQL Server 2005 Management Studio from Microsoft.com. Connect to your SQL Server; locate the database and table; Right click on the table and hit Export.

It should then give you the option as to what format you would like the data in.

Post your query...and I will give you the script...
The best way to export data from table to csv with any delimator, with use of SQLCMD, its command line utility.

More info abt the tool

example in Code Snippet

Note: Its required installation of sqlcmd Utility - its free

1. open Notepad 
2. Copy and paste the following Transact-SQL code into Notepad: 
USE AdventureWorks;
SELECT c.FirstName + ' ' + c.LastName AS 'Employee Name',
a.AddressLine1, a.AddressLine2 , a.City, a.PostalCode 
FROM Person.Contact AS c 
   INNER JOIN HumanResources.Employee AS e 
        ON c.ContactID = e.ContactID
    INNER JOIN HumanResources.EmployeeAddress ea 
        ON ea.EmployeeID = e.EmployeeID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = ea.AddressID;
Save the file as myScript.sql in the C drive.
now run the command in cmd.exe
sqlcmd -S myServer\instanceName -i C:\myScript.sql -s,
OR u can simple write like this on command line
sqlcmd -S myServer\instanceName -q "Select * from TableName" -i C:\myoutput.csv -s, -U loginID -p password

Open in new window

If you are exporting table just pass the tablename and filename with path and run the following code...

declare @sql varchar(8000), @tablename Varchar(1000), @FilenamePath Varchar(1000)
select @tablename = 'databasename.dbo.tablename', @FilenamePath = 'c:\filename.csv'
select @sql = 'bcp '+@tablename+' out   '+@FilenamePath+' -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql
I just did it on a 460K row table. Obviously too big for Excel but it did it in less than 30 seconds, created a .csv file.
In Query Analyzer, right click on Table as you did. Select open. If you click in that top left block - you got the right one, it should highlight the entire grid. Then do a save as. Is it not highlighting the entire grid?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.