Convert mysql database to csv
Recently I was asked by my friend to convert his whole database table to a something, so that he could distribute it around easily. As usual I created a sql dump for him by:
mysqldump -u username -ppassword database_name >table_data.sql
(Read more complicate ways at mydigitallife, which I didn’t try) 😛
But this wasn’t easy. The Data base was too large. The sql dump formed was of size 84MB!! A general windows mysql on xampp limits the import sql file to 25 MB!! So, this wasn’t at all a solution for him..
Then I came across a very brilliant page. The bash command given there is really awesome in terms of what it does. It uses a general mysql login, a sql query and sed parsing to put all the data in a .csv file which can be easily opened with any spreadsheet application.
Give it a try whenever you want to give your database to someone who doesn’t understand mysql. 😉
I used the command :
mysql -u root -psqlisgood mca -B -e "select * from \`company\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > data.csv
mysql username: root
mysql password: sqlisgood
query used: “select * from company”
P.S. You can try different queries to extract data as you want.. 😉