How to Perform MySQL Export to CSV?

Are you having difficulty performing MySQL export to CSV operation because there is too much confusion? You have just landed at the right post. We give you an easy, stepwise guide in 5 different ways to do just that.

MySQL is offered in two different editions: the open-source MySQL community server and the proprietary Enterprise server. However, only a limited number of applications support the raw format of MySQL export tables. Therefore, it is often beneficial to export MySQL tables to CSV format.

Introduction to MySQL

MySQL is considered to be one of the most popular Open-Source Relational Database Management Systems (RDBMS). MySQL implements a simple Client-Server Model that helps its users manage Relational Databases i.e. data stored in the form of rows and columns across tables. It uses the well-known query language, Structured Query Language (SQL), which allows users to perform all required CRUD (Create, Read, Update, Delete) operations.

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

CSV is a standard and lightweight format with several benefits, such as its simplicity, flexibility, and compatibility with various applications. Their straightforward structure makes them widely used across various industries. We need to use CSV for the following reasons:

• Store tabular data like spreadsheets and databases.
• Easily transfer data between different applications and systems.
• Import and export data in many software programs.
• Organize and manipulate data sets for analysis.
• Ideal for creating backups and archiving data due to their simplicity.

Methods to Perform MySQL Export to CSV

Database administrators and developers commonly export MySQL tables to CSV files. In this guide, we share five different methods for doing so. These methods include using command-line tools such as mysqldump csv and graphical user interfaces such as phpMyAdmin and MySQL Workbench.

Each method has advantages and disadvantages, and the choice of which method to use largely depends on the specific requirements of the task. By following the steps outlined in this guide, you will be able to successfully perform MySQL output to CSV file format, regardless of the method you choose.

• Using the command line
• Using mysqldump
• Using MySQL Workbench
• Using phpMyAdmin
• Using the CSV engine

1. Using Command Line

It is extremely easy to use the command line to perform MySQL export to CSV. You do not need to download any additional software. Read an in-depth article on the MySQL export database command line.

You will also learn how to perform MySQL export table to CSV using the command line under the following conditions:

• Exporting selected columns of a table
• Exporting tables with a timestamp
• Export with Column Headers
• Handling NULL Values

To be able to perform MySQL export to CSV, you need to ensure that the directory you are using has write permission granted to it.

To migrate your data from MySQL to CSV using the command line, you can run the following command:

SELECT *
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test;

After running this command, you will see something like this displayed on your screen:

• Make sure to use the .csv extension for your output file.
• The ORDER clause can arrange the data according to a particular attribute.
• The LIMIT clause restricts the number of rows copied into the output file.

a. Exporting Selected Columns of a Table

• To do this you can use the SELECT statement to specify the columns you want to export.
• You may additionally use the WHERE clause to use specific conditions and filter the results.

SELECT columnName, ….
FROM tableName
WHERE columnName = 'value';

b. Exporting Tables with a Timestamp

You may want to add a timestamp to the exported file, to do that you must use a MySQL prepared statement.

Use the following command to export to a CSV file and add a timestamp for the time the file was created:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '/var/lib/sql-files/';
SET @PREFIX = 'employees';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM tableName INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '"
' TERMINATED BY ',' 
ESCAPED BY '"'",
"LINES TERMINATED BY 'n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

c. Export with Column Headers

It is often convenient to add column headers to the output file to better identify and analyze the data. To do this, you must use the UNION statement.
Use the following command to add column headers:

(SELECT 'columnHeading', ...)
UNION
(SELECT column, ...
FROM tableName
INTO OUTFILE 'path-to-file/outputFile.csv’'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY 'n')

d. Handling NULL Values

If your results contain NULL values, they will appear as ‘N’ in the exported file instead of NULL. This may lead to confusion and thus, you may want to replace this ‘N’ string with a string like NA (not applicable) that makes more sense.
Use the following command to do it:

SELECT column, column, IFNULL(column, 'NA')
FROM tableName INTO OUTFILE 'path-to-file/outputFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"' 
LINES TERMINATED BY 'n');

2. Using mysqldump

mysqldump is a utility tool provided by MySQL server that enables users to export tables, databases, and entire servers. Moreover, it is also used for backup and recovery. Here, we will discuss how mysqldump csv can be used to perform MySQL export to CSV.

• Use the following command in a command prompt/terminal:

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

• The given command will create a copy of the table specified by tableName at the location you define using the -T option.
• The file’s name will be the same as that of the table and will have a .txt extension. This marks the completion of MySQL export to CSV operation using mysqldump.

3. Using MySQL Workbench

MySQL Workbench provides an Import/Export Wizard which allows you to export our database/ tables to a specified format using a graphical user interface. The wizard supports JSON and CSV formats to be able to seamlessly perform MySQL export to CSV operation. Read more about exporting data from MySQL workbench using 2 methods.

You can follow the given simple steps to operate MySQL export to CSV data transfer.

Step 1

• Use the left bar “schemas” tab to locate the table you want to export.

In this example, we will be exporting the employee’s table in the classic model’s database.

Step 2

• Right-click using your mouse on the table and select “Table Data Export Wizard” to get the following screen.
• Select the columns you want to export.

Step 3

• Click on Next.
• Browse to the directory where you want to save the output file.
• Choose the CSV format option.

Step 4

• Click on Next.
• Your data will start exporting.
• You can track the process through the logs.

 4. Using phpMyAdmin

phpMyAdmin provides a graphical user interface to export your MySQL table in different formats. Apart from CSV, it supports other formats such as XML, JSON, YAML, and many others. Using phpMyAdmin your users can output your tables in MySQL to CSV files, as depicted in the steps below.

To use phpMyAdmin to export data, follow these steps:

Step 1

• Log in to phpMyAdmin using a user that has required permissions.
• Navigate to the database which contains the source table as shown.

Step 2

• Choose the table from the database.
• Click on Export in the top bar.

Step 3

• Choose the CSV format from the format dropdown menu. This marks the completion of MySQL export to CSV operation.

• Click on Go.
• Select the save file option when prompted. This step will export your MySQL to CSV format for seamless data integration and analysis.

5. Using CSV Engine

The CSV storage engine stores data in text files using comma-separated values format and is always compiled into the MySQL server. It is important to note that this method to perform MySQL export to CSV can only be used if the table does not have an index or an AUTO_INCREMENT constraint.

ALTER TABLE tableName ENGINE=CSV;

This command changes the database’s format to CSV. It can then be easily copied to another system. Before wrapping up, let’s also examine the use of CSV.

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

Exporting data from a MySQL database into a CSV file offers several advantages that make data management and analysis easier and more efficient. A few key benefits are:

• They are compatible with other applications and systems, hence enabling easy transfer of data between them.
• They make it easier to import data into analysis applications like Excel, R, or Python.
• Simple backup of data. 
• Portable. 
• Easy export with minimal technical knowledge required. 
• CSV files are also easily incorporated into other systems for reporting and visualization.
• It is good for long-term data archiving.

Conclusion

Finally, you have learned five methods to perform MySQL export to CSV. If you are comfortable writing queries, using the command line or mysqldump utility tool will be the simplest. However, if you are not confident with your querying skills, MySQL export to CSV command line using MySQL Workbench and phpMyAdmin will be your best bet. 

Extracting complex data from diverse data sources such as MySQL can be challenging, and this is where Hevo comes in handy! Sign up for Hevo’s 14-day free trial and explore more such migrations.

FAQ on MySQL Export to CSV