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 the most popular open-source relational database. It stores data in the form of tables. It is offered under two different editions:
- The open-source MySQL community server
- The proprietary Enterprise server
However, the raw format of MySQL export tables is supported by a limited number of applications. Therefore, it is often beneficial to export MySQL table to CSV format.
Pre-requisites for performing MySQL export to CSV file:
- Basic knowledge of MySQL
- Using MySQL shell
- Using a terminal/command line
- Write permission for the intended output file
- Read permission for the input MySQL table
- Pre-configured phpMyAdmin account (optional)
CSV is a standard and lightweight format with several benefits such as its simplicity, flexibility, and compatibility with a wide range of applications. Features of CSV are as follows:
- CSV stands for comma-separated value and is a widely accepted format.
- CSV files have the added advantage of being human-readable.
- Being plain text, they can easily be imported into any application.
- Better at organizing large data.
Performing MySQL export to CSV can be beneficial when dealing with large datasets. With CSV, you can effectively organize and navigate through extensive data sets, making it easier to extract meaningful insights and perform data manipulations.
Hevo Data, a No-code Data Pipeline helps to integrate data from MySQL and 150+ data sources (including 50+ Free Data Sources) and load it in a Data Warehouse of your choice to visualize it in your desired BI tool.
Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
Get Started with Hevo for Free
Some of the salient features of Hevo include:
- Fully Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 150’s sources that can help you scale your data infrastructure as required.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.
Methods to Perform MySQL Export to CSV
Performing MySQL export to CSV files is a common task carried out by database administrators and developers. In this guide, we share five different methods to export MySQL table to CSV file format. These methods include using command-line tools such as mysqldump, as well as using graphical user interfaces such as phpMyAdmin and MySQL Workbench.
Each method has its advantages and disadvantages, and the choice of which method to use largely depends on the specific requirements of the task at hand. 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.
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. We have written an in-depth article also on MySQL export database command line.
You will also learn how to perform MySQL export to CSV using the command line under the following conditions:
To be able to perform MySQL export to CSV, do as follows:
- Navigate to the database which has the table you want to export using the following command:
Here, dbName must be replaced with the name of your database.
SHOW VARIABLES LIKE "secure_file_priv"
This command will show you the directory that has been configured. You can only store your output file in this directory.
- Select all the data from the table and specify the location of the output file.
INTO OUTFILE 'path/outputFile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY 'n';
- Make sure to use the .csv extension for your output file.
- The ORDER clause can be used to arrange the data according to a particular attribute.
- The LIMIT clause is used to restrict the number of rows to be 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, ….
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;
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', ...)
(SELECT column, ...
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 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 name of the file 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.
To download MySQL Workbench, click here.
You can follow the given simple steps to operate MySQL export to CSV data transfer.
- 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.
- 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.
- Click on Next.
- Browse to the directory where you want to save the output file.
- Choose the CSV format option.
- 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 download phpMyAdmin, click here.
To use phpMyAdmin to export data, follow these steps:
- Log in to phpMyAdmin using a user that has required permissions.
- Navigate to the database which contains the source table as shown.
- Choose the table from the database.
- Click on Export in the top bar.
- 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 format of the database to CSV. It can then directly be copied to another system easily.
You now have learned 5 methods to perform MySQL export to CSV in your arsenal. If you are comfortable with writing queries using the command-line or mysqldump utility tool will prove to be the simplest way.
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 a diverse set of data sources such as MySQL can be a challenging task and this is where Hevo saves the day!
Hevo offers a faster way to move data from 150+ data sources such as SaaS applications or Databases such as MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Have you used any of these methods for exporting MySQL to CSV? Have any further queries? Reach out to us in the comments section below.