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.

Rick MillsPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rick MillsPresidentAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Steve HoggITCommented:
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
Rick MillsPresidentAuthor 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
Steve HoggITCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.