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.
I translated this article for the Portuguese of Brazil. The automatic translation was very bad:
http://www.ajudalinux.org/wps/?p=91
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
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.
Where is this file saved?
Thank you!
Bianca
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?
Thanks a lot !!
Just what I needed š
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.
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!
Works smooth …….