With companies using a gazillion SaaS apps these days, syncing data between different apps can be a real pain.

Average number of SaaS apps used per organisation.
Average number of SaaS apps used per organisation. Source: https://www.bettercloud.com/monitor/the-2023-state-of-saasops-report/

If these apps don't share a common database, you've got two options:

  1. Integrate via API
  2. Transfer data in a universally accepted format, like CSV

Integration via API doesn't always work out, but don't worry – you can always fall back on CSV transfers as a solid alternative.

CSV has some pretty sweet advantages over Excel spreadsheets too:

  • It's an open format, meaning everyone can use it
  • It's a plain-text format, so no need for fancy software to read it
  • It stores data "as is," so you won't mess up your data (which often happens with Excel date variables, for example)

So, buckle up, folks! We've got an easy-to-follow tutorial on three ways to export SQL database to CSV: using SQL Server Management Studio, sqlcmd command line utility and n8n automation tool.

Don't want to scroll to the bottom? Grab this free n8n workflow template on how to export SQL data to CSV now!

How do I transfer data from SQL to CSV?

Let's dive into some cool ways to quickly export our MySQL data into a handy CSV file. We've got three nifty methods lined up for you:

  • Whipping up a CSV using SQL Server Management Studio (SSMS) – the classic method
  • Speeding things up with a command line utility, so you can skip opening SSMS every time
  • Harnessing the power of n8n, a low-code automation tool, so you can sleep tight at night while your data is in sync 24/7

Let's roll up our sleeves and find out how you can get more in less time with creative automation techniques!

Export SQL table to CSV via SQL Server Management Studio

One common way to perform SQL export to CSV is via SSMS. The SQL Server Import and Export Wizard is a popular tool among engineers. It allows for both manual data transfer as well as writing SQL queries. In this article, we'll demonstrate how to transfer data without running SQL statements. This technique is handy if you occasionally need to create CSV exports.

Step 1. Install SSMS and configure the local SQL Server

If you don’t already have a working SQL Server, we explained the installation process in another article on how to export MySQL to Excel.

Step 2. Connect to the SQL Server and export SQL table to CSV

Once you created a local SQL server and loaded a sample database, you can open the SQL Server Management Studio. Then connect to the database from the Object Explorer panel.

Connect to the SQL Server from SSMS
Connect to the SQL Server from SSMS

Once connected, right-click on the database name and select Tasks - Export Data. An Import and Export Wizard will open. We need to configure the Data source first. Select “Microsoft OLE DB Driver for SQL Server”, then press Properties and configure the connection. Here you need to provide a named pipe as a server name and select Windows Authentication. Don’t forget to Select the database! Finally, press Test Connection and OK once finished.

Add Data Source in the SQL Server Import & Export Wizard
Add Data Source in the SQL Server Import & Export Wizard

Next, we need to pick Flat File as a Destination. Select a File Name on your local drive and configure other settings, such as file locale and encoding. Press next and select whether you want to:

  • copy data from one or more tables or views, or
  • write an SQL query to specify the data to transfer

Pick the first option now. In the next window choose one source table. Let’s export the ProductCategory table. Follow the wizard until the end and open the created CSV file.

SQL table saved as CSV file
SQL table saved as CSV file
A quick lifehack: press the windows Start and search for “Import and Export Data”. This way you can start the export process without opening the SSMS.

MySQL export to CSV via command line

If you're allergic to clunky corporate interfaces, don't worry – a command line tool can save the day. For Windows users there's the sqlcmd utility. Just type sqlcmd -? to see if it's already installed. If it's not, check out the Microsoft documentation page on sqlcmd for more guidance.

Step 1. Get familiar with sqlcmd

The sqlcmd tool offers a range of options that you can explore by typing sqlcmd -? in the command line:

List of sqlcmd options
List of sqlcmd options

Step 2. Export SQL query to CSV file

Let’s export the same table without further ado:

sqlcmd -S \\.\pipe\MSSQL$SQLEXPRESS\sql\query ^
-Q "select top 10 * from AdventureWorksLT2019.SalesLT.ProductCategory;" ^
-o "<YOUR PATH HERE>\Export_test1.csv" ^
-W ^
-s ","
  • Specify your server name in the -S option
  • Write SQL query inside the -Q option
  • Provide output file in the -o option
  • -W removes trailing spaces
  • Lastly, choose a delimiter symbol with the -s option
  • SELECT TOP 10 is for illustrative purposes only

As you can see, the CSV export file is created but it looks a bit odd:

Default CSV export from the sqlcmd utility. Note the unnecessary data in lines 2 and 14
Default CSV export from the sqlcmd utility. Note the unnecessary data in lines 2 and 14
  • Line 2 has unnecessary dashes
  • At the end of the file there is an extra line (10 rows affected)

We can fix this with a different call:

sqlcmd -S \\.\pipe\MSSQL$SQLEXPRESS\sql\query ^
       -W ^
       -s "," ^
       -Q "set nocount on; select * from AdventureWorksLT2019.SalesLT.ProductCategory;" | findstr /v /c:"---" /b > "<YOUR PATH HERE>\Export_test2.csv"

Let’s take a closer look:

  • Here we provided an extra option set nocount on; in the -Q option
  • We didn’t specify the -o option. Instead, we dump the output right in the command line window and use the findstr tool to search for lines containing dashes. The /v option ensures that this extra line will not be saved in the output file.

Congrats! You have successfully made an SQL export to CSV file with headers.

Export SQL query to CSV using n8n

The first two steps have a common downside – they take some manual effort. This can get tiresome when you regularly export lots of data. But with the n8n tool, you can output SQL query to CSV file automatically.

The best thing about this approach is how straightforward it is. You can carry out routine tasks without any manual effort or coding. With n8n's help, you can either save the CSV file or move data from SQL into various other systems.

If you want to later extend your workflow, here are some ideas: join data from Postgres and MySQL or sync Pipedrive and MySQL.

In this section of the tutorial, we'll explore how to export data from SQL server to CSV automatically. We'll demonstrate how to create a simple automation that saves you from mundane work.

Step 1. Get data from SQL to n8n

n8n workflow to export SQL query into a CSV file
n8n workflow to export SQL query into a CSV file

This example workflow shows how to convert SQL to CSV file. You can achieve the goal with just four nodes!

Starting node activates the process when you press Execute Workflow.

Next goes the Set node. String variable TableName is declared there and it´s equal to SalesLT.ProductCategory – same table as in previous example. Set node allows us to automatically create CSV file name.

The third node is a MSSQL node that connects to a database. You´ll need to set up credentials first. If you are running a local installation, some additional tweaks are required, because LocadDB uses Windows user’s credentials by default to access the database.

After this, pick Execute Query operation and provide the following Query as an expression:

SELECT * FROM {{ $json["TableName"] }}

As you can see, we pick all records from a table whose name we defined in a Set Node. If everything is right, you´ll see the imported data:

SQL table imported into n8n
SQL table imported into n8n

Step 2. Convert the table to a CSV format

Last node converts a JSON object into an CSV file. Please check the configuration of the Move Binary Data Node:

Assign CSV file name automatically
Assign CSV file name automatically

File Name option is an expression and it is set to:

{{ $node["TableName"].json["TableName"] }}.{{ $parameter["fileFormat"] }}

Here we generate File Name automatically based on

  • the table name that was provided in Set Node and
  • a File Format parameter. If you change the format and pick XLS (old Excel format), the File Name will change automatically to SalesLT.ProductCategory.xls. This trick can be useful when you export SQL database to CSV programmatically.

Sheet Name option is also an expression:

{{ $node["TableName"].json["TableName"] }}

We make sure that the CSV file name is equal to an SQL table name.

Now you can see how easy it is to export SQL query to CSV automatically!

Wrap Up

In this tutorial, we learned how to export SQL server data to CSV using three different techniques.

  • We did it via Microsoft SQL Server Management Studio
  • Another approach was to use the sqlcmd command line utility
  • Finally, we exported the SQL database to CSV in n8n. It allowed us to achieve our goal without writing any code

What’s next?

So, you've spent a truckload of time gathering data in a centralized MySQL database. Here's what you can do next:

Start automating today and register your n8n account in the cloud for free! You can run it on your own server.