Skip to content →

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:

For example:

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:

For example:

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

Published in Software

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *