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.

rickmillsPresidentAsked:
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.

0
 
rickmillsPresidentAuthor Commented:
Mohammed,
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.
0
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.
 
0
 
MohammedUCommented:
You don't need to buy any tool...Data transformation Services (DTS) tools comes with SQL server...

Check the following...
http://www.sqldts.com/276.aspx
0
 
rickmillsPresidentAuthor Commented:
HoggZilla,
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,
Rick
0
 
St3veMaxCommented:
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.

HTH
0
 
MohammedUCommented:
Post your query...and I will give you the script...
0
 
vbsquickresponseCommented:
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
http://msdn.microsoft.com/en-us/library/ms162773.aspx

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

0
 
MohammedUCommented:
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
0
 
HoggZillaCommented:
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?
0
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.