Export MySQL database table to CSV (delimited / Excel) file

Export MySQL database table to CSV (delimited / Excel) file

Today lets talk a little about converting a MySQL table to CSV (Excel). My friend was looking to export MySQL to Excel, I saw couple of questions for export MySQL tables to CSV on forums. Since I saw the question often, I thought of writing out all the ways I can think of for exporting Delimited (CSV / TSV / …) data from MySQL table. Pretty chewed & basic but frequent topic.

Following are the ways to export CSV data from MySQL database / table(s).

1. Using SELECT INTO … OUTFILE statement to export from MySQL to CSV

SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. Just to mention, SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE, which you may use to load CSV (generally speaking delimited) files to MySQL.

Here’s a sample command to export “tablename” table of “db” database as a CSV:

SELECT * INTO OUTFILE '/csv_files/db.tablename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;

Since above command will export only single table as a csv file; let’s see how can we do this for more tables. In this case export all the tables in MySQL database.

– We will use information_schema to prepare syntaxes for exporting tables to CSVs and export them to a temporary file:

mysql> select concat('SELECT * INTO OUTFILE "/var/lib/mysql-files/CSV_exports',TABLE_SCHEMA,'.',table_name,'.csv" FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY "\\n" FROM ', table_schema,'.',table_name,';') backup_csv from information_schema.tables where table_schema not in ('information_schema','sys','mysql','performance_schema','test') into outfile '/var/lib/mysql-files/export_csv';      

– Now since our commands are ready, we’d just run by executing the same. We can source the export_csv file we created in previous command.

mysql> source /var/lib/mysql-files/export_csv

Once the source command is complete, queries are done, you’d be able to see your CSV files under /var/lib/mysql-files/CSV_exports directory.

There is one thing you may need to watch out for – secure-file-priv. If your server is running with secure-file-priv set, you may see following error for our execution of SELECT … INTO OUTFILE command:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. 

Actually this option restricts both the import (eg. LOAD … DATA INFILE) and export (eg. SELECT … INTO OUTFILE) operations.

When secure_file_priv=””, there is no restriction.
When secure_file_priv=”/path/dir”, import export are allowed only with the files in that directory.
When secure_file_priv=NULL, all the import / export operations are disabled. (Introduced from 5.7.6)

2. Using the CSV Engine to export from MySQL to CSV

MySQL supports the CSV storage engine, which stores data in text files in comma-separated values format.
This sounds easy, all you need to do is to change the database engine for the table to CSV and copy the file!

ALTER TABLE innodb_to_csv ENGINE=CSV;

After the ALTER command you will have 3 files: CSV, CSM and FRM in your data-directory. You may directly copy the CSV file.

[root@kedar ~]#  ls -lhtr /var/lib/mysql/test/a.*
-rw-r----- 1 mysql mysql 8.4K Jan 26 11:45 /var/lib/mysql/test/innodb_to_csv.frm
-rw-r----- 1 mysql mysql  956 Jan 26 11:45 /var/lib/mysql/test/innodb_to_csv.CSV
-rw-r----- 1 mysql mysql   35 Jan 26 11:45 /var/lib/mysql/test/innodb_to_csv.CSM
[root@kedar ~]#

Spoiler: This will work only if your table doesn’t have an index, because CSV storage engine doesn’t support indexes. meh!

Anyhow, depending on what your requirement is, I’d surely recommend using copy of the table to convert to CSV in order to avoid troubling the original table. You may choose to follow next steps.

Since CSV table support no indexes, we will create a table without indexes:

mysql> CREATE TABLE csv_table AS SELECT * FROM innodb_table LIMIT 0;

Convert the table to CSV:

mysql> ALTER TABLE csv_table ENGINE=CSV;

Load the data:

mysql> INSERT INTO csv_table SELECT * FROM innodb_table;

You can then use the csv_table.CSV from data directory.

3. Using mysqldump to export from MySQL to CSV

After all the hardwork above, here’s another way to export MySQL database table to CSV (or delimited text for that matter); and this one I believe is the easiest.

The mysqldump is widely used client utility to perform logical backups and hence well known. Though it is less common (my guess) to use the command to generate output in CSV (delimited) or XML format.

Just like our SELECT … INTO OUTFILE statement, we can specify options to create the delimited files from mysqldump command. In below command we’re exporting all the tables of “mytest” database as a CSV file:

mysqldump --tab=/var/lib/mysql-files/ --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' mytest

This will create SQL and TXT files as an output under the directory specified with –tab option. Sample execution below:

[root@kedar ~]# mysqldump --tab=/var/lib/mysql-files/ --fields-enclosed-by='"' --fields-terminated-by='\n' mytest
[root@kedar ~]# ls -lhtr /var/lib/mysql-files/
...
-rw-r--r-- 1 root  root  1.6K Jan 26 11:39 app_user.sql
-rw-rw-rw- 1 mysql mysql 367K Jan 26 11:39 app_user.txt
-rw-r--r-- 1 root  root  2.1K Jan 26 11:39 random_data_gen.sql
-rw-rw-rw- 1 mysql mysql 1.3M Jan 26 11:39 random_data_gen.txt
-rw-r--r-- 1 root  root  2.6K Jan 26 11:39 splits.sql
-rw-rw-rw- 1 mysql mysql  13M Jan 26 11:40 splits.txt
-rw-r--r-- 1 root  root  1.4K Jan 26 11:40 sbtest1.sql
-rw-rw-rw- 1 mysql mysql 589K Jan 26 11:40 sbtest1.txt
...

There you go, the .txt files are your CSV tables. You can filter table data or automate the export as per requirement from the shell script quite easily.

There are surely other ways to export from MySQL like using MySQL WorkBench, PHPMyAdmin & other GUI tools or even scripting your way out.