Comma Separated Values ​​(CSV) files are a way to transfer data between applications. Databases like MySQL and spreadsheets like Excel support import and export via CSV, so you can use CSV files to exchange data between the two.

CSV files are plain text, so they are naturally lightweight and easy to export from MySQL.

From the database server

If you have access to the server where MySQL is running, you can export a selection with the INTO OUTFILE order.

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

This will produce a CSV file to /tmp/mysqlfiles/table.csv, or wherever you have configured it. You will need to make sure that the user running MySQL (usually mysql or root) has ownership and write access to the directory.

You will also need to make sure that the secure_file_priv The parameter allows MySQL to access this directory. This, by default, blocks read and write access from SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker would only have access to MySQL, not the rest of the filesystem.

You can whitelist specific directories by editing your MySQL configuration file (usually located at /etc/my.cnf) include:

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

This will allow MySQL to read and write to /tmp/mysqlfiles/ (which you will need to create with mkdir). Once MySQL can export files, you should be able to run the query and generate CSV files.

With the ENCLOSED BY parameter, commas will be correctly escaped, for example:

"3","Escape, this","also, this"

That you can take and import directly into any spreadsheet or other software.

Comma separated list worksheet.

Keep in mind that the exported CSV file does not include the column headers, but the columns will be in the same order as the SELECT declaration. Additionally, null values ​​will be exported as N, which is an expected behavior, but if you want to change that you can change the selection by wrapping ifnull(field, "") around your fields in your SELECT declaration.

From the MySQL command line

If you only have command line access to the MySQL instance, and not to the server itself (for example, when it is not managed by you, in the case of Amazon RDS), the problem is a bit trickier. So that you can use FIELDS TERMINATED BY ',' on the server to generate a comma separated list, the MySQL CLI will separate with tabs by default.

Just enter a query from the command line and direct it to a file:

mysql -u root -e "select * from database;" > output.tsv

Since MySQL output is tab separated, this is called a TSV file, for “tab separated values”, and may work in place of your CSV file in some programs like spreadsheet imports. But it’s not a CSV file, and converting it to one is complicated.

You can simply replace each tab with a comma, which would work but fail if there are commas in the input data. If you are 100% sure that there are no commas in your TSV file (check with grep), you can replace the tabs with sed:

sed "s/t/,/g" output.tsv > output.csv

But if you have commas in your data, you will need to use a much longer regular expression:

sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

This will correctly escape fields with quotes, which will solve the comma problem.

Note: the tab character t is not standard. On macOS and BSD it is not available, resulting in a mess of every lowercase “t” causing sed to insert erroneous commas. To resolve this issue, you will need to use a literal tab character instead of t:

sed "s/  /,/g" output.tsv > output.csv

If your input data contains tabs, you are out of luck and will have to generate a CSV file yourself with scripting language.

Do it manually with a real programming language

MySQL (and most databases) are designed to interact with it, so you probably already have some sort of programming language connected to MySQL. Most languages ​​can write to disk as well, so you can create your own CSV output scripts by reading the fields directly from the database, correctly escaping them, and writing a comma delimited file.

An example in Python.

LEAVE A REPLY

Please enter your comment!
Please enter your name here