Export Mysql Database into a CSV File

Sponsored Link

If you want to export your mysql databases into a csv file here is simple tip for you.

You need to run the following command from your terminal

mysql -u exampleuser -p letmein 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

"id","username","group","password"
"1","male","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"2","newton","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"3","ruchi","admin","5f4dcc3b5aa765d61d8327deb882cf99″
"4","ruchi1″,"staff","5f4dcc3b5aa765d61d8327deb882cf99″
"5","tej","staff","5f4dcc3b5aa765d61d8327deb882cf99″
"6","tej1″,"admin","5f4dcc3b5aa765d61d8327deb882cf99″

And now for the explanation:

Starting with the MySQL command.

The -u option is you need to enter the username

The -p option is you need to enter the password

"exampledb" -- Database name

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.

filename.csv ---> Name of the file you want to export.

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

Sponsored Link

9 thoughts on “Export Mysql Database into a CSV File

  1. Sheeri what you suggest is different in the case the server and the client are different machines, which is the most likely in the real world.

    “Select * INTO OUTFILE” can only output to the server host machine.
    If you want an output file on your client machine, mysql -e is a good option.

    Unless you want to scp to your server… assuming that you even have the permission to write somewhere.

  2. Hi!

    I tried this command of MySQL on Ubuntu 8.10 but it says:

    bash: .: filename argument required
    .: usage: . filename [arguments]
    sed: -e expression #1, char 1: unknown command: `.’
    bash: s/^/./: No such file or directory
    bash: s/$/./: No such file or directory
    bash: s/n//g.: No such file or directory

    What is exact command I should run?

  3. It is possible to export to csv without all the ” ?

    I removed:
    ;s/$/ā€/; —> This will place a ā€ at the start of the line.
    s/\n//g —-> This will place a ā€ at the end of the line.

    But there are ” between the columns.

  4. If you run into errors like:

    sed: -e expression #1, char 1: unknown command: `?’
    -bash: s/^/ā€/: No such file or directory
    -bash: s/$/ā€/: No such file or directory
    -bash: s/n//gā€™: No such file or directory

    Like ‘Vitalie’ does (and I did), please make sure there are no fancy quotes in your line. When I copy/pasted the sed line from this website, through TextMate, into my console, I got nice curly-ish (double)quotes. When I deleted those in the line, and replaced them with the normal quotes ‘ and “, everything worked exactly as it should.

    Thanks for this awesome post!

Leave a comment

Your email address will not be published. Required fields are marked *