• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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.

0
rickmills
Asked:
rickmills
  • 5
  • 2
  • 2
  • +2
2 Solutions
 
rickmillsAuthor 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
 
MohammedUCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HoggZillaCommented:
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
 
rickmillsAuthor 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now