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

Advertisements
Explore posts in the same categories: Uncategorized

Tags: ,

You can comment below, or link to this permanent URL from your own site.

2 Comments on “Convert mysql database to csv”

  1. Marcelo Altmann Says:

    Great Post my friend.
    Sometimes I had to export data from mysql to csv.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: