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.
You may also be interested in...
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