If you need the data from a table or a query in a CSV fiel so that you can open it on any spreadsheet software, like Excel you can use something like the following:
SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1
Or you can use sed:
mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
Explanation:
username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export
The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three 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.
You can find the exported CSV file in the current directory. The name of the file is filename.csv.
However if there are a lot of tables that you need to export, you'll need a script like this:
#!/bin/bash
#### Begin Configuration ####
DB="mydb"
MYSQL_USER="root"
MYSQL_PASSWD='mypass'
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL="/usr/bin/mysql"
#### End Configuration ####
MYSQL_CMD="$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWD -P $MYSQL_PORT -h $MYSQL_HOST"
TABLES=`$MYSQL_CMD --batch -N -D $DB -e "show tables"`
for TABLE in $TABLES
do
SQL="SELECT * FROM $TABLE;"
OUTFILE=$TABLE.csv
$MYSQL_CMD --database=$DB --execute="$SQL" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $OUTFILE
done
Just be sure to change the configuration section to meet your needs.
Name the file something like: export_csv.sh and be sure to make it executable. In Linux, do something like:
chmod +x ./export_csv.sh
If you want to have all of the exported files in a certain directory, you could either modify the script or just make the cirectory, "cd" into it, and then run the script. It assumes you want to create the files in the current working directory.
To change that behavior, you could easily modify the "OUTFILE" variable to something like:
OUTFILE="/my_path/$TABLE.csv"
