How to Export Data From MySQL to a CSV File

This tutorial will introduce how to save the output of a MySQL query into a CSV file.

MySQL has an easy technique to export the output of a select query into a text or CSV file on the server. Using intricate commands like INTO OUTFILE, we can quickly and efficiently write into a CSV file.

Let us create a table and store its results in a CSV file.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. In order to view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now, let us try to save the results of the above query in a separate CSV file.

MySQL Export to CSV

One of the most basic methods to export MySQL data into CSV is using the OUTFILE keyword. We can make use of this keyword as shown below:

-- Exporting data into students.csv file
SELECT stu_id, stu_firstName, stu_lastName
FROM student_details
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/abc.csv';

The code above writes the query’s output into a file named abc.csv in the C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ directory.

The output of the above mentioned code generates the following file:

The data in the above-mentioned file can be seen as follows:

We can also try to format the output inside the CSV file. This can be done with the help of FIELDS TERMINATED BY, ENCLOSED BY '""' , and LINES TERMINATED BY commands. We can use these commands as follows:

-- Exporting data into students.csv file
SELECT *
FROM student_details
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/abc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The output of the code above results in the CSV file as follows:

As we can see, the output in the above image is formatted correctly with a tab-separated file.

Thus, with the help of the methods above, we can efficiently export our data into a CSV file. To locate this CSV file, we can follow the path given in the INTO OUTFILE command in the code blocks mentioned above.