Methods to Export a CSV file from MySQL Command Line

Spread the love


Comma Separated Values recordsdata (CSV) are a method of transferring information between functions. Databases like MySQL and spreadsheet software program like Excel help importing and exporting by CSV, so you need to use CSV recordsdata to trade information between the 2.

CSV recordsdata are plaintext, in order that they’re naturally light-weight and simple to export from MySQL.

From The Database Server

In case you have entry to the server that MySQL is working on, you possibly can export a variety with the INTO OUTFILE command.

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

This may output a CSV file to /tmp/mysqlfiles/desk.csv, or wherever you configured it to. You’ll want to ensure the person working MySQL (normally mysql or root) has possession and write entry to the listing.

You’ll additionally want to ensure the secure_file_priv setting permits MySQL to entry that listing. This, by default, blocks learn and write entry from SQL queries. This can be a good factor; in case your code is weak to SQL injection, any potential attacker would solely have entry to MySQL, and never the remainder of the filesystem.

You may whitelist particular directories by enhancing your MySQL config file (normally situated at /and so on/my.cnf) to incorporate:

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

Which can enable MySQL to learn and write to /tmp/mysqlfiles/ (which you’ll must create with mkdir). As soon as MySQL can export recordsdata, you must be capable of run the question and output CSV recordsdata.

With the ENCLOSED BY setting, commas might be correctly escaped, e.g.,:

"3","Escape, this","additionally, this"

Which you’ll be able to take and import immediately into any spreadsheet program or different software program.

Spreadsheet of a comma-separated list.

Remember the fact that the exported CSV file doesn’t embody column headings, however the columns might be in the identical order because the SELECT assertion. Additionally, null values might be exported as N, which is predicted conduct, however in the event you’d like to vary this you possibly can modify the choice by wrapping ifnull(area, "") round your fields in your SELECT assertion.

From The MySQL Command Line

When you solely have command line entry to the MySQL occasion, and never entry to the server itself (comparable to when it’s not managed by you, within the case of Amazon RDS), the issue is slightly trickier. Whereas you need to use FIELDS TERMINATED BY ',' on the server to generate a comma-separated record, the MySQL CLI will separate with tabs by default.

Merely enter a question from the command line, and pipe it to a file:

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

As a result of MySQL output is separated with tabs, that is known as a TSV file, for “tab-separated values,” and may go instead of your CSV file in some applications like spreadsheet imports. Nevertheless it isn’t a CSV file, and changing it to 1 is difficult.

You could possibly merely substitute every tab with a comma, which might work however would trigger it to fail if there are commas within the enter information. When you’re solely 100% sure that there aren’t any commas in your TSV file (test with grep), you possibly can substitute the tabs with sed:

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

However in case you have commas in your information, you’ll have to make use of a for much longer regex:

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

This may correctly escape fields with citation marks, which is able to remedy the comma downside.

Notice: the tab character t just isn’t customary. On macOS and BSD, it’s not out there, which ends up in a multitude of each lowercase “t” inflicting sed to insert faulty commas. To unravel this, you’ll want to make use of a literal tab character instead of t:

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

In case your enter information accommodates tabs, you’re out of luck, and should generate a CSV file your self with a scripting language.

Do It Manually With a Actual Programming Language

MySQL (and most databases) are designed to be interacted with, so that you doubtless have some type of programming language related to MySQL already. Most languages may write to disk, so you possibly can create your personal CSV output scripts by studying fields from the database immediately, escaping them correctly, and writing a comma-delimited file.

An instance in Python.

Leave a Reply

Specify Twitter Consumer Key and Secret in Super Socializer > Social Login section in admin panel for Twitter Login to work

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