How to Export a CSV file from MySQL Command Line

Transport of data between applications.

Comma-separated value files (CSVs) 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 in plain text, so they are naturally light and easy to export from MySQL.

From the database server

If you have access to the server on which MySQL is running, you can export a selection with the INTO OUTFILE command.

SELECT id, column1, column2 FROM table
INTO OUTFILE ‘/tmp/mysqlfiles/table.csv’
FIELDS ENDED WITH ‘,’
ENCLOSED BY ‘”‘
LINES ENDED WITH ‘ n’

This will generate a CSV file in /tmp/mysqlfiles/table.csv, or wherever you have configured it. You 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 parameter allows MySQL to access this directory. By default, this 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 file system.

You can add specific directories to the whitelist by modifying your MySQL configuration file (usually located in /etc/my.cnf) to 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, the commas will be escaped correctly, for example:

“3”, “Escape, this”, “also, this”

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

Comma-separated list worksheet.

Remember that the exported CSV file does not include column headers, but the columns will be in the same order as the SELECT statement. Also, null values ​​will be exported as N, which is expected behavior, but if you want to change it, you can change the selection by wrapping ifnull (field, “”) around your fields in your SELECT statement .

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 little more delicate. Although you can use FIELDS TERMINATED BY ‘,’ on the server to generate a comma-separated list, the MySQL CLI will separate with default tabs.

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

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

As the MySQL output is tab separated, this is called a TSV file, for “tab separated values”, and can work in place of your CSV file in some programs like importing spreadsheets. 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 cause it to fail if there are commas in the input data. If you are 100% sure that there is no comma 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 escape fields properly with quotes, which will solve the comma problem.

Note: the tab character t is not standard. On macOS and BSD, it is not available, which causes a mess of every tiny “t” resulting in the insertion of erroneous commas by sed. To resolve this issue, you will need to use a literal tab character in place of t:

sed “s / /, / g” output.tsv> output.csv

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

Do it manually with a real programming language

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

An example in Python.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.