EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Export to CSV

MySQL Export to CSV

By Payal UdhaniPayal Udhani

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL Export to csv 1

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:

MySQL Export to csv 2

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

opened in Microsoft Excel of LibreOffice calc

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:

MySQL Export to csv 4

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

opened in Microsoft Excel of LibreOffice calc

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.

MySQL Export to csv 6

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.

MySQL Export to csv 7

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.

overwrite the file

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

MySQL Export to csv 9

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:

MySQL Export to csv 10

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.

button that turns into opening the files

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.

Recommended Articles

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()
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more