Feb 062007
 

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

 Posted by at 6:15 pm
  • http://www.ajudalinux.org Thiago

    I translated this article for the Portuguese of Brazil. The automatic translation was very bad:

    http://www.ajudalinux.org/wps/?p=91

  • http://www.sheeri.com Sheeri

    You can also use

    SELECT x,y,z INTO OUTFILE ‘/path/to/file’
    FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
    FROM tbl WHERE ….

    see http://dev.mysql.com/doc/refman/5.0/en/select.html

  • jaybee

    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.

  • Bianca

    Where is this file saved?

    Thank you!

    Bianca

  • Vitalie

    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?

  • http://lab.kapit.fr Steeve Cayla

    Thanks a lot !!
    Just what I needed :)

  • bryan

    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.

  • http://www.wolerized.com/ Remi Woler

    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!

  • Tanuj

    Works smooth …….