Export from MySQL to CSV

Using command line tools to export data from a MySQL database into a CSV file is quite easy. Here's how:

mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Here is some sample output of the above:


And now for the explanation:

Starting with the MySQL command. I wont explain the -u and -p options they are straight forward (if in doubt man mysql). The -B option will delimit the data using tabs and each row will appear on a new line. The -e option denotes the command to run once you have logged into the database. In this case we are using a simple SELECT statement.

Onto sed. The command used here contains three seperate 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.

After running the result set through sed we redirect the output to a file with a .csv extension.

Regards, Bawdo2001