Exporting from MySQL to CSV file

We often have to export data from MySQL to other applications; this could be to further analyse the data, gather user emails for a newsletter or similar. Usually these applications, CSV is probably the most common format for data exporting like this.

Luckily SQL select output, with its rows and columns, if well suited for CSV output.

You can easily export straight from the MySQL client to a CSV file, by appending the expected CSV format to the end of the query:

[SQL QUERY]
INTO OUTFILE '[FILE]'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

For example:

SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The last three lines can be customised based on your needs.

NOTE: The default MySQL settings only allows writing files to the /var/lib/mysql-files/ directory.

If you do not have permissions to write files from the MySQL client, the same can be accomplished from the commandline:

echo "[QUERY]"|mysql -u [DBUSER] -p [DBNAME]|sed s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > [FILE]

For example:

echo "SELECT * FROM users"|mysql -u my_user -p my_database|sed s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > users.csv

The regex is, of course, courtesy stackoverflow.

Regex Explanation:

s/// means substitute what's between the first // with what's between the second // the "g" at the end is a modifier that means "all instance, not just first" ^ (in this context) means beginning of line $ (in this context) means end of line So, putting it all together:

s/'/'/ replace ' with ' s/\t/","/g replace all \t (tab) with "," s/^/"/ at the beginning of the line place a " s/$/"/ at the end of the line place a " s/\n//g replace all \n (newline) with nothing