Export Mysql Data to CSV file

Export Mysql Data to CSV file

In this article I'll show you how to export MySQL database to a CSV file.

1. Export MySQL data to CSV file using a simple "SELECT" statement

If you want to export your mysql table into a csv file you need to run the following command:

# mysql -u username -ppassword database -e "SELECT * INTO OUTFILE '/tmp/filename.csv'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\r\n' FROM table;"

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

You can find the exported CSV file in the /tmp directory. The name of the file is filename.csv.

2. Export MySQL data to CSV file using "sed"

If you want to export your mysql table into a csv file you need to run the following command:

# mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Explanation:

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three sed scripts:

s/\t/","/g;s/^/"/ - this will search and replace all occurences of 'tabs' and replace them with a ",".

s/$/"/; - this will place a " at the start of the line.

s/\n//g  - this will place a " at the end of the line.

You can find the exported CSV file in the current directory. The name of the file is filename.csv.