January 25, 2011

SQL Server: Export Table to CSV




Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

Create a test table

CREATE TABLE test(
empid varchar(6),
empname varchar(100),
dob datetime,
salary decimal(12,2)
)

Consider the following data

INSERT INTO test
SELECT 'EMP001','Suresh','19910619',3000
UNION ALL
SELECT 'EMP002','Ramesh','19710103',20000
UNION ALL
SELECT 'EMP003','Nilesh','19800722',4760
UNION ALL
SELECT 'EMP004','Kumar','19680911',42000

Method 1: Use bcp in command prompt

Usually when we use bcp to export data, by default column values are separated by a tab.
So we need to use a format file to make the column delimiter a comma instead of a tab.

Create a file named format.fmt in F drive (or whichever drive is available in your system)
with the following data. You can also download format.fmt over here

format.fmt

Over here, 9.0 refers to the SQL Server Edition (in this case SQL Server 2005) and 4 represents the number of rows (in this case 4 rows).

Now open the command prompt and use the following bcp command in it

bcp yourdb..test out D:\test.csv -T -f D:\format.fmt

The file named F:\test.csv will be created with values separated by comma.

Method 2: Use Management studio

Right click on the database and select Tasks > Export data

Select Data source as SQL Server, select the server name, authentication and database and click Next

Export Csv SSMS

Select Destination as Flat file Destination and browse for a .csv file and click Next

Export Csv SSMS

Export Csv SSMS

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

Export Csv SSMS

At the end it will show the details of the rows which got exported to .csv file.

Export Csv SSMS

Note: If you intend to do the reverse, i.e. Import .csv into a table, check my previous article Load Comma Delimited file (csv) in SQL Server


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

3 Responses to "SQL Server: Export Table to CSV"
  1. server management said...
    January 26, 2011 at 4:09 AM

    This was a controversial matter to us a long day ago. Now it comes true and we understand the reality.

  2. Anonymous said...
    January 26, 2011 at 8:56 PM

    nice approach using fmt. You can also do it Invoking CLR User-Defined Aggregate Functions - Check here http://technet.microsoft.com/en-us/library/ms131056.aspx

    Gopal

  3. Kerala Traveller said...
    April 2, 2013 at 2:02 AM

    But for sql express all these suggestions were in vain and finally we went the php way.. http://www.php-trivandrum.org/tips/mssql-export-to-csv/

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions