MySQL Export to CSV

Introduction to MySQL Export to CSV

The following article provides an outline for MySQL Export to CSV. In MySQL, we store the data in the database in table format. You often need to retrieve the data stored in the tables of a MySQL database in some other form, such as CSV, JSON, or SQL format. This is often required when we transfer data and share between multiple companies and individuals. Suppose we have exported data of the database and its table contents in CSV format. In that case, this data can be further used in other platforms such as google docs, open office, or Microsoft Excel for further analysis of the resultset and concluding the necessary statistics by performing various operations on the exported data to get certain conclusions.

In this article, we will see how we can make use of the INTO OUTFILE statement to export the data of MySQL database and its contents to any CSV format using the command-line MySQL shell, and we can export the data of MySQL to CSV format when we are using the MySQL Workbench editor with the help of an example.

Exporting MySQL to CSV

Before exporting to CSV format, we need to make sure that no other file with the same name as mentioned in the query exists on the same path as used, and also, the server of MySQL has to write permissions on that path or folder specified where our CSV file will be saved.

Consider the following query statement that retrieves the data of developers whose name begins with R.

Code:

select * from developers where name like "R%";

Output:

To export the resultset to a CSV file, we will use the INTO OUTFILE clause, as shown below.

Code:

select * from developers where name like "R%" INTO OUTFILE '/var/lib/mysql-files/R_developers_cmd.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Output:

When opened in Microsoft Excel of LibreOffice calc, your exported CSV file will look as follows.

You can see that only the data is exported, but column headings are not exported. If you want to export the heading names of the column, you can use the following query statement.

Code:

select 'developer_id', 'team_id', 'name', 'position', 'technology', 'salary' UNION select * from developers where name like "R%" INTO OUTFILE '/var/lib/mysql-files/R_developers_cmd_with_headings.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Output:

When opened in Microsoft Excel of LibreOffice calc, your exported CSV file will look as follows.

Exporting Data to any other Format in Workbench

You can export the table contents and structure by going to the schemas tab in the Workbench tool’s left sidebar. Then you can right-click on the table of the database you wish to export and then click on “Table Data Export Wizard” which will lead to the opening of the export wizard as shown in the below image, where you will see the list of all the columns of that table that are auto-selected for exporting. You can choose the columns you want to export by changing the selected columns and then clicking on the Next button at the bottom, as shown below.

If you click the advanced button below, you can type the equivalent query whose resultant data and content will be exported. After clicking on the following button, you can choose the path where the file needs to be exported and the type of format in which you want to export, either CSV or JSON and in the case of CSV, you can select the options associated with data to be exported and specifying different properties that need to be applied while exporting data such as field separator to be considered and NULL word interpretation, etc. as shown below.

When you click Next, if the same file with the same name and format already exists on the chosen path, a question appears asking if you wish to overwrite the file. If not, it lists the actions that will be taken and done during exporting, as shown below.

After clicking next, you will see a message that the file has been exported successfully, as shown below.

After successfully running the export process, you can observe that the program will create the exported file in the specified path in the desired format.

Exporting the Query Resultset

There is also the provision to export the resultset of any select query executed in Workbench by simply clicking on the export button and then specifying the path and name of the file. Note that in MySQL, using the command line, we can export the query results by using the INTO OUTFILE statement. Let us look at how we can export the resultset of the query in the workbench with the help of an example.

Consider the following select query that retrieves the names of all developers that begin with the letter R, as shown below.

Code:

select * from developers where name like "R%";

Output:

Now, we can click on the export button that turns into opening the files, and we can choose the path and name of the file by typing in it, as shown below.

You can choose the report format from CSV, HTML, XML, JSON, SQL, or Excel, whichever you want, from the choice list provided below in the left-hand corner. We will choose the CSV option to export the data to CSV format. Click on the save, and your file will be exported with the retrieved result set.

Conclusion

We can export the files using the command line, MySQL query statements, and various options provided in the MySQL Workbench client tool. We can specify the carriage return values, file and line separators, and even the value with which the column value should be enclosed in both methods.

We hope this EDUCBA information on “MySQL Export to CSV” benefited you. You can view EDUCBA’s recommended articles for more information.

1. Natural Join in MySQL
2. MySQL ROW_NUMBER()
3. DATE_SUB() MySQL
4. MySQL DECODE()