Export MySQL data to CSV file

As much as importing data into MySQL is helpful, you also might like to export your data to CSV file format that is widely supported. You can open your data with spreadsheet like Excel, Libreoffice Calc, Google sheets and more. There are various techniques of how to export a MySQL table to a CSV file, but I'm going to demonstrate two methods.

Method 1: MySQL

Following SQL query will dump all the records from a table called "person" into the file /tmp/export_person.csv as a CSV file.

SELECT * from person INTO OUTFILE '/tmp/export_person.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

Take note of that the directory must be writable by the MySQL database server. Also that it will not overwrite the file if it already exists.

Method 2: Command line

You can also use command line in Linux to get your report. To get the same data as the example above

$ mysql -u dbusername -pdbpassword exampledb -B -e "select * from \'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export_person.csv

• mysql : the executable file (should be where MySQL is installed)
• -u dbusername : a switch and option for the username to execute the SQL with
• -p dbpassword : a switch and option for the password of the user
• exampledb : the database to run the SQL against
• -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
• -e “the sql statement here” : the SQL statement to run returning your data
• | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
• > export_person.csv : outputs the results to the file named export_person.csv

I hope that this is somewhat helpful.

See also Import csv to MySQL