- Debian Admin - http://www.debianadmin.com -
Export Mysql Database into a CSV File
Posted By Admin On 6th February 2007 @ 18:15 In Database | 3 Comments
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...
Article printed from Debian Admin: http://www.debianadmin.com
URL to article: http://www.debianadmin.com/export-mysql-database-into-a-csv-file.html
Click here to print.