How to export table to CSV in MySQL

CSV file is a very common text file format that is supported by many applications. The full form of CSV is Comma-Separated Values. That means data are separated by a comma in each line of this file. It is mainly used to exchange tabular data between different types of applications that support this file format. XML is another file format that commonly used for exchanging data between applications. But CSV file is a better option than the XML file, because It takes less space and bandwidth than an XML file. You require to export data in CSV format from one application before import the data into another application. MySQL supports various ways to export data from database tables. CSV format is one way of them. This article shows different ways to export data in CSV format from the MySQL database table.

Prerequisite:

Before running any export statement, you have to create a new database and tables or select an existing database and tables. Run the following commands to create a library database and three tables named books, borrowers and book_borrow_info.

CREATE DATABASE library;
USE library;

CREATE TABLE books (
id INT NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
author varchar(50) NOT NULL,
publisher varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE borrowers (
id VARCHAR(50) NOT NULL,
name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE book_borrow_info (
borrow_id VARCHAR(50),
book_id INT,
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
status VARCHAR(15) NOT NULL,
INDEX par_ind (book_id),
PRIMARY KEY (borrow_id, borrow_date),
FOREIGN KEY (book_id) REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE );

Run the following SQL commands to insert some data into three tables.

INSERT INTO books VALUES
(NULL, 'To Kill a Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
(NULL, 'One Hundred Years of Solitude', 'Garcia Marquez', 'Lutfi Ozkok'),
(NULL, 'Invisible Man', 'Ralph Ellison', 'Encyclopadeia Britannica, Inc.');

INSERT INTO borrowers VALUES
('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH', '[email protected]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', '[email protected]'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME',
'[email protected]');

INSERT INTO book_borrow_info VALUES
('123490', 1, '2020-02-15', '2020-02-25', 'Returned'),
('157643', 2, '2020-03-31', '2020-03-10', 'Pending'),
('146788', 3, '2020-04-10', '2020-01-20', 'Borrowed');

Every exported file stores in a particular location in MySQL and the location is stored in the variable, “secure_file_priv“. Run the following command to find out the path of the file. This is a read-only variable that you can’t change.

SHOW VARIABLES LIKE "secure_file_priv";

The location of the file is ‘/var/lib/mysql-files/’. You have to use this location at the time of running export command.

Export data in CSV format using INTO OUTFILE statement:

Any database table can be exported by using INTO OUTFILE statement. Suppose, I want to export the data of the books table. Run the following command to check the existing data of this table.

SELECT * FROM books;

Any filename with .csv extension can be set for the export filename. Run the following export command to create books.csv file in the location,/var/lib/mysql-files/.

SELECT title, author, publisher FROM books INTO OUTFILE '/var/lib/mysql-files/books.csv';

Go to the export location to check the file is created on not. Click on the file to display the content of the file. If the comma exists as a content of any field value of the table then it will separate the value into multiple values. In the books table, publisher field contains a comma(,) as content in the third record. If you examine the content of the CSV file you will see that the publisher content is separated into two values that are not appropriate.

To solve the above problem, you can export the books table data into a CSV file by specifying the necessary delimiters properly in the SQL statement. Run the following command to export books table into books2.csv file appropriately. Here, three delimiters are used to export data properly. These are FIELDS TERMINATED BY, ENCLOSED BY and LINES TERMINATED BY.

SELECT title, author, publisher FROM books
INTO OUTFILE '/var/lib/mysql-files/books2.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Now, if you open the books2.csv file then you will see the previous problem is solved in this file and the data will comma(,) is not divided into multiple values.

Export data in CSV format using mysql client:

The table data can be exported into a CSV file by using mysql client. The borrowers table is exported in this part of this article. Run the following command to check the existing content of this table.

SELECT * FROM borrowers;

mysql statement with `sed` command is used here to export the data. One of the benefits of this statement is that you can set any location and filename for storing the CSV file without the default location that is used in the previous example. If there is no password for the root user then -p option will be omitted. The exported location is /tmp and filename is output.csv here.

mysql -h localhost -u root -p -e 'select * from library.borrowers' |
sed 's/\t/,/g' > /tmp/output.csv

If you open the output.csv file then the following output will appear.

Export data in CSV format using phpmyadmin:

The database table can be exported into CSV file very easily by using any database administrative tool. You have to install the tool before doing the export. phpmyadmin is used here for exporting the table into the CSV file. Here, the exported filename will be the name of the table. Run the following URL in any browser to display the existing database list of the MySQL server.

http://localhost/phpmyadmin

Select the table of the database that you want to export and click on the Export tab from the right side. Select the CSV format from the Format drop-down list and click on the Go button. Select the Save File option and press the OK button.

The file will be downloaded in the Downloads folder. Here, book_borrow_info table is exported. So, the CSV filename will be book_borrow_info.csv and the following content will appear if you open the file.

Conclusion:

The data exchange task between the applications becomes easier by using the export feature of the database server. MySQL supports many other file formats to export table data. The sql file format is mainly used to transfer data between the same database servers. CSV format is very helpful if you want to exchange data between two different applications.