How to Export Data from MySQL into a CSV File

A database is a primary platform for working with data and storing data. But often, you must take your data out of the database. Read on to find out how to export data from MySQL database into a CSV file.

In this article, we’ll demonstrate how to export data into a CSV file. We’ll start by introducing what a CSV file is and why we use it. Then, we’ll export data from a MySQL database into a CSV file. You can do that in two ways: using a command line prompt or a graphical user interface (GUI).

Before we start, make sure you’ve installed a MySQL database on your PC. And to get a head start, check out our course on SQL Basics in MySQL to learn or review the core commands of MySQL. You can read more about our MySQL courses here.

Let’s get started.

A CSV File: What It Is and Why We Use It

CSV stands for “Comma-Separated Values”. A CSV file is a text file that stores tabular data. Each line of a CSV file represents a row in a table. The column values are usually separated by a comma (hence the name, comma-separated values), but you can use other separators, such as a semi-colon or a tab. Typically, the first row of a CSV file stores column headers and the following rows store data.

Let’s look at a sample database table and the CSV file created from it.

Here is a table:

And here is a corresponding CSV file:

Id,FirstName,LastName,SSN
35,Jonathan,Glover,111222333
36,Jennifer,Brown,444555666
37,Christopher,Watson,777888999

It’s as easy as that!

Now let’s answer the question of why you would want to use a CSV file. A CSV file is the simplest and most efficient way to move data across databases; this file format is compatible with any relational database. It takes little storage (depending on the amount of data, of course), and can be converted to almost any system-compatible file format for further processing.

Here are some examples of how essential simple CSV files are. You can use them to:

• Export data from a MySQL database for further processing in a different tool.
• Move data from one database to another without any compatibility issues.
• Import data into a database.

Our focus in this article is exporting data from a MySQL database. Read on to find out what you can do with your exported data.

Why Export Data from a MySQL Database into a CSV File?

Let’s answer the question of why to export data from a MySQL database, or any other database, into a CSV file.

After exporting data into a CSV file, you can:

• Transfer data to another database by importing a CSV file.
• Upload a CSV file into different software tools for further processing, such as a spreadsheet or a reporting tool.
• Analyze the data offline.
• Make a graph or a chart based on CSV file data.
• Input the data into reports.

Now it’s time to see how it’s done in MySQL.

Exporting Data from a MySQL Database into a CSV File

Here is a step-by-step guide on how to connect to a MySQL database via command line tool or a graphical user interface (GUI) and how to export data into a CSV file.

If you’d like to practice with us, install the MySQL database on your PC to follow along with the steps!

Using Command Line

Connecting the Database

You can connect to a MySQL database using a simple command prompt like this:

To do so, use the following command:

mysql --host=localhost --user=username --password

After that, enter your password:

Enter password: *******

And you’re connected!

Exporting the Data

Now let’s export our data into a CSV file. Here is the table that will be exported:

And here is how to export data from MySQL into a CSV file using a command line tool:

To do so, use a SELECT statement to select data to be exported and, at the end, define a file to store the exported data:

SELECT *
FROM data.employees
INTO OUTFILE 'employees.csv';

If you are on Windows, the CSV file is most probably available at C:\ProgramData\MySQL\MySQL Server 8.0\Data.

Let’s try exporting data that requires a slightly more complicated query to be retrieved from the database. Here is the table that will be exported:

And here is how to export data from MySQL into a CSV file using the command line:

It is analogical to the previous export. You define a SELECT statement and a file to store the exported data:

SELECT e.Id, e.FirstName, e.LastName, e.SSN, ea.Street, ea.ZipCode,
       ea.City, ea.State, ea.Country
FROM data.employees e
JOIN data.employees_address ea
ON e.Id=ea.Id
INTO OUTFILE 'employees_addresses.csv';

INTO OUTFILE 'employees_addresses.csv';

Again, if you are on Windows, the CSV file is most probably available at C:\ProgramData\MySQL\MySQL Server 8.0\Data.

Go ahead and try exporting other data tables using the command line tool!

Using GUI

Connecting the Database

Let’s connect to MySQL using its graphical user interface (GUI), MySQL Workbench.

MySQL Workbench lets you connect to any database by clicking the + sign next to MySQL Connections on the home page. A window pops up that requires you to provide connection details, such as the connection name, connection method, hostname, port, username, and password.

Once you input the connection details, test the connection by clicking the Test Connection button.

After a successful test, you get this message:

Exporting the Data

Now let’s export our data into a CSV file. Here is the table that will be exported:

And here is how to export data from MySQL into a CSV file using the MySQL Workbench GUI:

1. Right-click on the table to be exported and choose Table Data Export Wizard.

2. Define data you want to export, such as columns, row offset (number of exported rows counted from the bottom), count (number of exported rows counted from the top).

3. Define the file path, file format (here, csv), line separator (LF, CR, or CR LF, depending on your operating system), character in which to enclose string values, and value/field separator.

4. Here is the summary of the export tasks:

5. If the export is successful, you’ll get this message:

6. And the final screen:

The exported file is waiting for you in the defined location.

Let’s try exporting data that requires a slightly more complicated query to be retrieved from the database. To use the Table Data Export Wizard, we first create a view using our complex query and then export this view.

First, let’s create the view:

CREATE VIEW data.employees_addresses AS (
SELECT e.Id, e.FirstName, e.LastName, e.SSN, ea.Street, ea.ZipCode,
       ea.City, ea.State, ea.Country
FROM data.employees e
JOIN data.employees_address ea
ON e.Id=ea.Id);

Now we can export this view using the Table Data Export Wizard, as before, to get the CSV file.

The import process is easier to perform using the graphical user interface. However, the export process is easier to perform using a command line tool. Check out our article on how to import a CSV file to a MySQL database to see how to do the reverse of this process!

Let’s Export Some Data!

Now you know how to export data from MySQL into a CSV file. And if you followed the linked article on how to import a CSV file to MySQL, you are familiar with the import process as well. But that’s just for MySQL!

To learn about data export and import processes in other database engines, we encourage you to check out our articles on how to import CSV files to PostgreSQL using PgAdmin and how to export CSV files from Microsoft SQL Server using both a graphical user interface and a command line tool.

If you do your work in MySQL, check out our MySQL Cheat Sheet, which is a quick reference guide for MySQL syntax, and our guide to MySQL date functions for data analysts.

Have fun!