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

  9 Responses to “Export Mysql Database into a CSV File”

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

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

  2. 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

  3. 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.

  4. Where is this file saved?

    Thank you!

    Bianca

  5. 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?

  6. Thanks a lot !!
    Just what I needed :)

  7. 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.

  8. 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!

  9. Works smooth …….

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>