Export Mysql Database into a CSV File
Posted by Admin on February 6th, 2007
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
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.
February 7th, 2007 at 5:03 pm
I translated this article for the Portuguese of Brazil. The automatic translation was very bad:
http://www.ajudalinux.org/wps/?p=91
February 10th, 2007 at 3:39 am
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
August 7th, 2008 at 6:20 am
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.
October 16th, 2008 at 9:46 am
Where is this file saved?
Thank you!
Bianca
February 13th, 2009 at 5:15 pm
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?
March 17th, 2009 at 10:20 am
Thanks a lot !!
Just what I needed