MySQL : Export / Select Query to get data as CSV

Export from MySQL to CSV:

Using command line tools to export data from a MySQL database into a CSV file is quite easy. 

Here's how:
mysql -u
ktpot -pktpot ktpotdb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ktpot.csv


Here is some sample output of the above:
"id","username","group","password"
"1","tux","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"2","tlugian","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"3","saiyuki","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"4","fred","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"5","barney","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"6","wilma","admin","5f4dcc3b5aa765d61d8327deb882cf99"

Explanation:

-u --> user
-p --> password
-B option will delimit the data using tabs and each row will appear on a new line.
-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.

Example:

SELECT a, b, c FROM ktpot INTO OUTFILE 'D:/testing/ktpot.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'.

Taking MySQL Dump :


mysqldump -uroot -pktpot --skip-extended-insert ktpotdb employee > ktpot_employee.sql
mysqldump --extended-insert=false -uroot -pktpot ktpotdb employee > ktpot_employee.sql

0 comments: