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:
- Using the Command Line
- Using Mysqldump
- Using phpMyAdmin
- Using the CSV Engine
- Using MySQL Workbench
- 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.
- To open a command prompt, press the Windows button + R and type cmd in the run window.
- A command line interface will appear on your screen. Verify if MySQL is running by executing the
net startcommand in the command prompt. You will see a list of services that are currently running.
- 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.
- You can log in to your MySQL local server as the root user using
mysql -u root -pcommand.
- On execution of the above command, you will be prompted to enter the password.
Enter password: *******
- Now, you’re connected to the MySQL server. The command prompt will now look like this:
- Move to the MySQL folder using the cd command and list the files in this folder using dir command.
cd c:\Program Files\MySQL
- To check the list of databases, enter the below command.
- To select a specific database, use the name of the database that you want to use.
[databasename]with the name of the database you want to select.
- 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
- Use the below command to export data from MySQL to CSV.
SELECT * FROM data.employees
INTO OUTFILE 'employees.csv';
SELECTstatement is used to get the data from the
data.employeestable and the data is exported in the
INTO OUTFILEstatement will load the data into the
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:
- Export data with a column heading
- Handling NULL values
- 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.
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
SELECTstatement 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.
plaintextSELECT 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.
plaintextSELECT 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.
plaintextmysqldump -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
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
[database], use the
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.
Image Credit: inmotionhosting.com
Step 3: After selecting an Export method option, you need to select the format of your exported file. Now, choose the CSV from the dropdown list under the format menu.
Click on Go. Next, click on the Save button.
4. Using the CSV Engine
The CSV store engine is one of the ways to export to CSV from MySQL. It uses CSV format to house data in text files.
Once you create a table, the server will create a .csv extension file name. The file name will be the same as that of the table name. It stores data using comma-separated values format, as shown below in the output.
plaintextCREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV; Query OK, 0 rows affected INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0
After executing the above commands, you will get a test.csv file in the database directory. The data in the file should look like this.
plaintext"1","record one" "2","record two"
5. Using MySQL Workbench
MySQL Workbench provides import/export wizard that allows you to import or export your database or specific tables. The wizard uses a Graphical User Interface (GUI) and supports CSV and JSON format.
Click here to download MySQL Workbench.
Follow the below steps to export MySQL output to CSV files.
Step 1: Open MySQL Workbench and select the database. Right-click on the table name and choose ‘Table Data Export Wizard.’ You can also perform this step from the tab menu, click ‘Server’, and select ‘Data Export.’
Image Credit: dev.mysql.com
Step 2: After selecting the ‘Table Data Export Wizard,’ you will see the below window. By default, all the tables will be selected from that schema. You can uncheck if you don’t want to export any table. Click Next.
Image Credit: dev.mysql.com
Step 3: Next, you will get a ‘Table Data Export’ window. Here, you can browse the path to store your file. Choose the CSV format. Click Next.
Image Credit: dev.mysql.com
Step 4: In the ‘Export Data’ window, check if ‘Prepare export’ and ‘Export data to file’ are selected. Then click Next. Your data will start exporting, and you can track the process through Logs.