How to Export Data From Microsoft SQL Server to a CSV File

When working with data and databases, it is common to export data for further processing or transport to another database. Follow this article to learn how to export data from Microsoft SQL Server to a CSV file.

In this article, we first recall what a CSV file is and why you would want to export data in this format from an MS SQL Server database. Then, we cut to the chase and export data to a CSV file using both SQL Server Management Studio (SSMS) and SQL Command Line (SQLCMD).

Before we start, we encourage you to install the Microsoft SQL Server database along with MS SQL Server Management Studio so you can follow along.

Check out our course on SQL Basics in MS SQL Server, which covers basic concepts of T-SQL including querying and aggregating data, JOINs, subqueries, and more.

Let’s get started.

A Quick Review of the CSV File

CSV stands for comma-separated values. The name reveals quite a bit. A CSV file is a plain text file that stores data in a tabular format.

Each line in a CSV file represents one data row. The values within each data row are separated, commonly by a comma as the CSV format name indicates. But other separators such as a semi-colon or a tab may also be used.

It is widely accepted that the first line of a CSV file stores the column headers, and the following lines store data. Let’s look at a sample database table and a corresponding CSV file.

Here is the table:

And here is the corresponding CSV file:

Employee_Id,First_Name,Last_Name,Email,Phone,Position,Salary
1,Claire,Brown,claire@zoo.com,123123123,Manager,4000
2,James,Anderson,james@zoo.com,456456456,Caretaker,3500
3,Jessica,Dowson,jessica@zoo.com,789789789,Assistant,3500

CSV files are the simplest files for storing and transferring large amounts of data. Read on to see why CSV files are so vital when working with data and databases.

Why Export Data From Microsoft SQL Server to a CSV File?

There are many reasons for exporting data from Microsoft SQL Server to a CSV File. Let’s list some of the common things you can do with the exported data.

• You can transfer data in the form of a CSV file to another database. Importing a CSV file into a database is as easy as exporting it!
• You can upload a CSV file to various software tools, such as a spreadsheet or a reporting tool, for further processing.
• CSV format enables us to analyze the data offline.
• You can make graphs and charts based on data in a CSV file.
• As CSV file format does not imply any complicated rules for storing data. It lets you create any form of report easily.

Let’s start exporting data!

Exporting Data From Microsoft SQL Server to a CSV File

The Microsoft SQL Server database provides both a graphical user interface (GUI) and a command line tool. The GUI is called SQL Server Management Studio, or SSMS. And the command line tool is called SQLCMD.

Here, we go through the steps describing how to connect your database and export data using both GUI and command line tools.

Using SQL Server Management Studio

Let’s use SQL Server Management Studio to export data to a CSV file.

Connecting to the Database

Once you install Microsoft SQL Server along with SQL Server Management Studio, open SSMS. You see a popup window asking you to provide the connection credentials.

In the Authentication drop-down, choose the authentication method. Some of the common authentication methods are Windows Authentication and SQL Server Authentication. Here, we continue with the Windows Authentication option by clicking Connect.

For more details on connecting and using SQL Server Management Studio, visit the official Microsoft website here.

Now we are ready to export some data!

Exporting Data

SQL Server Management Studio provides SQL Server Import and Export Wizard to export data to a CSV file.

Let’s open and work with the SQL Server Import and Export Wizard.

Right-click the name of the database from which you want to export data to a CSV file, choose Tasks, and then, Export Data.

You see a welcome screen like the one below. Click Next.

Choose a data source from which to export data. Click Next.

Choose a destination where to copy the data and click Next. Please note that the CSV file does not need to exist as the wizard creates it when exporting data.

Choose the data to be exported and click Next. You may either export one or more tables or specify an SQL query to define the data to be exported. Here, we choose to export a table.

Specify a table for export and click Next. You may also choose a row delimiter (typically a new line character) and a column delimiter (typically a comma).

We are now ready to run the export process. You may choose to save the SSIS package that includes the components for executing the ETL tasks.

Here is the final report before running the export process. Click Finish to export the data to a CSV file.

And here is what you get upon successful execution of the export process.

Close the wizard window and go to the location where the export file is saved.

Now that you have exported a CSV file from your Microsoft SQL Server database, you may want to import it into another database. Here is how to import a CSV file into a PostgreSQL database using PgAdmin, and here is how to import it into a MySQL database using either GUI or a command line tool.

Using SQLCMD

Now, let’s see how to export a CSV file using SQLCMD.

Here is the official Microsoft website on the SQLCMD utility. Let’s go through the steps of how to connect your database and export a CSV file using SQLCMD.

Connecting to the Database

We again use the Windows Authentication option. This is how you connect your database in SQLCMD:

Use the sqlcmd command with the -S parameter that stands for the server name and pass your computer name as an argument:

sqlcmd -S DESKTOP-EU2MTDD

After that, you see the prompt 1> indicating the connection is successful.

Exporting Data

Once the connection is established, query the data you want to export.

Exit by typing exit at the prompt. Run the sqlcmd command to export our table to a CSV file.

Here is the command we use to export the data:

sqlcmd -S DESKTOP-EU2MTDD
       -Q "select * from Zoo.dbo.Zoo_Employees;"
       -o "C:\Users\Martyna\zoo_employees.csv"
       -W
       -w 1024
       -s ","
       -h-1

Let’s analyze it:

• sqlcmd is the command name.
• -S DESKTOP-EU2MTDD specifies the server name.
• -Q "select * from Zoo.dbo.Zoo_Employees;" is the query that fetches data to be exported.
• -o "C:\Users\Martyna\zoo_employees.csv" defines the file that stores the exported data.
• -W removes the trailing white spaces you see in the command line tool output after each value.
• -w 1024 defines the length of the line in the CSV file.
• -s "," defines the separator for the column values; here, a comma.
• -h-1 removes the header.

And here is the exported data:

That’s it! Go ahead and practice with your own tables!

What’s Next?

To make your knowledge even stronger, make sure to check out our course on SQL Basics in MS SQL Server. And if you want more, here are the Top 5 MS SQL Server Courses for Beginners.

Have fun!