MySQL to CSV: 5+ Methods to Export Data

MySQL databases are a common way to manage and store a wide variety of structured data. However, you would often need to migrate data from your MySQL database to another. There are many ways to migrate data. One of the popular methods to migrate data is to export MySQL data in CSV files. But, migration is not limited to just moving between two databases. There are many reasons to export MySQL data with CSV files.

Following are some of the reasons for MySQL to CSV export.

• You can build reports from the exported CSV data.
  
• You can easily access your database data in offline mode.
  
• Several SaaS tools accept CSV files to import or export data.

This article will demonstrate 5 ways to export data from MySQL to CSV. However, let’s first understand what CSV and MySQL are in brief. 

What is a CSV file?

A CSV (Comma Separated Value) file is a plain text file used to store data. The values in CSV files are separated using commas. Since separating data with commas is a simple process, the CSV files are lightweight. As a result, it is a popular choice for exporting and importing data among data professionals. 

Although the data in CSV files are separated in comma-delimited format, you can quickly convert them into a columnar format. This allows you to quickly read and analyze data with different programming languages.

What is MySQL?

MySQL is an open-source Relational Database Management System. It is widely used to store structured data to perform quick analysis. With MySQL, you can store data across multiple tables and apply the relationship among data points for better accessibility. 

MySQL works with Structured Query Language (SQL), making it accessible to a wider range of users. However, you can also talk to the MySQL database with different programming languages like Python, Java, and more.  

Methods of Exporting Data to CSV from MySQL Database

In this section, you will learn how to export your databases or tables from MySQL to CSV files: 

1. Using the Command Line
2. Using Mysqldump
3. Using phpMyAdmin
4. Using the CSV Engine
5. Using MySQL Workbench
6. SaaS Alternatives

1. Using the Command Line

You can use the command line method to establish a connection with your MySQL database and export data into a CSV file.

1. To open a command prompt, press the Windows button + R and type cmd in the run window. 
   
2. A command line interface will appear on your screen. Verify if MySQL is running by executing the net start command in the command prompt. You will see a list of services that are currently running. 
   
3. Connect with your local MySQL server using the below command:
   
   mysql -u [username] -p
   
   Enter the username at [username] that you created for MySQL installation.
   
4. You can log in to your MySQL local server as the root user using mysql -u root -p command.
   

4. On execution of the above command, you will be prompted to enter the password. 
   
   Enter password: *******
   
5. Now, you’re connected to the MySQL server. The command prompt will now look like this:
   
   mysql>
   

6. Move to the MySQL folder using the cd command and list the files in this folder using dir command.
   
   cd c:\Program Files\MySQL

dir
   
7. To check the list of databases, enter the below command.
   
   show databases;
   
8. To select a specific database, use the name of the database that you want to use.
   
   use [databasename];
   
   Replace [databasename] with the name of the database you want to select.
   
9. Now, let’s export the data in a CSV file. Let’s consider the below MySQL database table for exporting in a CSV file.

Image Credit: learnsql

11. Use the below command to export data from MySQL to CSV. 
    
    SELECT * FROM data.employees 
INTO OUTFILE 'employees.csv';
    
    Here, the SELECT statement is used to get the data from the data.employees table and the data is exported in the employees.csv file. INTO OUTFILE statement will load the data into the employees.csv file.
    
    If you are using Windows, your file will be stored at the C:\ProgramData\MySQL\MySQL Server 8.0\Data location.
    
    The above method will export data into CSV without any specific customization. You can follow the below methods to add column names, export specific columns of a table, or handle null values.
    
    Using the command line method, you can also export customized data from MySQL to CSV with the following approaches: 
    
    
    1. Export data with a column heading
    2. Handling NULL values
    3. Export specific columns of a table

Export Data with a Column Heading

It would be easier to understand data if the exported CSV files contained a column heading as their first line. Use the UNION operator to add a column heading to the CSV file. 

As shown in the command below, you need to add a column heading for every column.

Code: 

plaintext
(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber, orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY ' " ' TERMINATED BY ';' ESCAPED BY ' " '
LINES TERMINATED BY '\r\n');

Let’s understand the query in more detail:

• In the first SELECT statement mention the column heading, add the column heading that you want in the output file. 
  
• In the OUTFILE, mention the path where you want to store your csv file with .csv extension.

Handling NULL Values

Often your tables might contain NULL values which would be shown as ‘N’ in the exported file. You can change this ‘N’ with your preferred text for better understanding using the IFNULL function. 

Code:

plaintext
SELECT empName, empjoinDate, IFNULL(empNumber, 'N/A') 
FROM employee INTO OUTFILE 'C:/tmp/employeedata2.csv' 
FIELDS ENCLOSED BY ' " ' 
TERMINATED BY ';' 
ESCAPED BY ' " ' LINES 
TERMINATED BY '\r\n';

In the above query statement, the ‘Null’ values in the empNumber column are replaced with ‘N/A’ string.

Export Specific Columns of the Table

For more convenient use, you would want to select specific columns from a table. Using the SELECT statement and WHERE clause, you can achieve filtered results. For reference, check the below command lines.

plaintext
SELECT employeeName,  
FROM Employee
WHERE employeeName= 'value'; 
INTO OUTFILE 'employees.csv';

In the above query, employeeName is the column name and Employee is the table name. Using the WHERE clause, you can select specific values from the column. With this method, you can export specific columns and values from the table. 

2. Using mysqldump

mysqldump is one of the functions used to perform logical backups of MySQL databases. For backup and transfer tasks, mysqldump dumps one or more databases into the SQL server. Using this function, you can also generate output in XML, CSV, or other delimited formats.

To perform a MySQL dump to CSV, use the following command in the terminal.

Code:

plaintext
mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

The above command will create a copy of the table, as mentioned in [tableName], at a specified location/path, which is defined using -T flag. 

A CSV file will be created with a .csv extension, and the file name will be the same as that of the table name. 

To dump/export the entire database, do not use [tablename] after [database], use the --databases or --all-databases option.

3. Using phpMyAdmin

With phpMyAdmin, you can export your databases in CSV, XML, YAML, and JSON formats. It provided a Graphical User Interface (GUI) to export data from the database to any other format.

Below is the step-by-step guide to exporting data using phpMyAdmin.

Step 1: Log in to your phpMyAdmin. Next, choose the database and select the table you want to export. Now, click the ‘Export’ button from the top menu bar.

Image Credit: inmotionhosting.com

Step 2: Next, you need to select the ‘Export Method’. In order to export all the data from the table, select the ‘Quick’ option. You can choose the 'Custom' option if you want to export specific data from the table.