MySQL Command Line – How to export a CSV file from CloudSave IT

  • Home / Tech Today / MySQL Command Line…

MySQL Command Line – How to export a CSV file from CloudSave IT

Comma Separated Value Files (CSV) is a way to transfer data between applications. Dikes like Mike MySQL and spreadsheet software 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 in which MySQL is running, you can export a selection with it INTO OUTFILE Command.

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

This will output a CSV file /tmp/mysqlfiles/table.csv, Or wherever you have configured it. You need to make sure the user is running MySQL (usually) mysql Or rootHas ownership and written access to the directory.

You also have to make sure secure_file_priv Configuration gives MySQL access to this directory. This, by default, prevents access to MySQL queries from reading and writing. That’s a good thing. If your code suffers from MySQL injection, any potential attacker will only have access to MySQL, not the rest of the file system.

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

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

Which will allow MySQL to read and write /tmp/mysqlfiles/ (With which you have to create mkdir). Once it can export MySQL files, you should be able to run queries and export CSV files.

with the ENCLOSED BY By setting, commas will escape correctly, such as:

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

Which allows you to import directly into any spreadsheet program or other software.

Comma-separated list spreadsheet.

Keep in mind that the exported CSV file does not include column headings, but the columns will be in the same order as SELECT Statements and values ​​will also be exported N, Which is expected behavior, but if you want to change it you can wrap and edit the selection ifnull(field, "") Around your fields SELECT Statement

From the MySQL command line

If you only have command line access to MySQL for example, and do not have access to the server itself (such as when it does not run through you, in the case of Amazon RDS), the problem is a bit more difficult. Is. While you can use FIELDS TERMINATED BY ',' To create a comma-separated list on the server, MySQL will be separated by default with LL tabs.

Just enter a query from the command line, and pipe it into the file:

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

Because MySQL output is separated with tabs, it is called a TSV file, because “tab-separated values”, and in some programs can work in place of your CSV file. Spreadsheet imports. But this is not a CSV file, and converting it to one is complicated.

You can easily replace each tab with a comma, which will work but fail if you put a comma in the input data. If you are 100% sure that your TSV file does not have a comma (check it out) grep), You can change the tab with it sed:

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

But if your data has a comma, you will have to use a very long regex:

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

This quote will correctly escape from the marked fields, which will solve the problem of coma.

Note: Tab Character t Not standard. On Mac OS and BSD, it’s not available, which disrupts every “T” at the bottom sed To insert the wrong comma. To solve this you’ll need to use the literal tab character in place t:

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

If your input data includes tabs, you’re out of luck, and have to create a CSV file yourself from the scripting language.

Do this manually with a real programming language

Designed to interact with MySQL (and most databases), so you already have some kind of programming language attached to Mike MySQL. Most languages ​​can also write to disk, so you can read fields directly from the database, escape them correctly, and create your own CSV output script by writing a comma-deleted file.

An example in Python.

Write a Comment

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

x