قالب وردپرس درنا توس
Home / Tips and Tricks / Export a CSV file from the MySQL command line – CloudSavvy IT

Export a CSV file from the MySQL command line – CloudSavvy IT



  Transport data between applications.

Comma Separated Values ​​(CSV) files are a way of transferring data between applications. Databases like MySQL and spreadsheet software like Excel support importing and exporting 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. [1

9659004] From the database server

If you have access to the server running MySQL, you can export a selection using the command INTO OUTFILE .

  SELECT id, column1, column2 FROM table
IN PROGRESS & # 39; /tmp/mysqlfiles/table.csv'
FIELDS ENDED BY & # 39;, & # 39;
CLOSED BY & # 39; "& # 39;
LINES ENDED BY & # 39;  n & # 39; 

This will output a CSV file to /tmp/mysqlfiles/table.csv or wherever you have configured it. You must ensure that the user has MySQL (usually mysql or root ) owner and write access to the directory.

You also need to make sure that the secure_file_priv setting gives MySQL access to that directory. By default, this blocks read and write access to SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker can only access MySQL and not the rest of the file system.

You can whitelist specific folders by editing your MySQL configuration file (usually found at / etc /my.cnf"19459007]) including:

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

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

With the ENCLOSED BY setting, commas are correctly escaped, for example:

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

which you can directly into any spreadsheet program or other software.

 Spreadsheet from a comma-separated list.

Keep Note that the exported CSV file does not contain column headers, but the columns are in the same order as the SELECT statement. Also null values ​​are exported as N which is normal behavior, but if you want to change this you can change the selection by ifnull (field, "") [19459007intepakken] 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 access to the server itself (such as when it is not managed by you, in the case of Amazon RDS), the problem is Little trickier. Although you FIELDS TERMINATED BY & # 39;, & # 39; On the server to generate a comma-separated list, the MySQL CLI is separated by tabs by default.

Simply 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-delimited, it is called a TSV file for & # 39; tab-delimited values ​​& # 39; and it can work instead of your CSV file in some programs like importing spreadsheets. But it is not a CSV file and converting it to one is complicated.

You could easily replace any tab with a comma, which would work, but would cause it to 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 have to use a much longer regex:

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

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

Note: The tab character t is not standard. On macOS and BSD, it's unavailable, leading to a mess of every lowercase & # 39; t & # 39;, inserting sed erroneous commas. To fix this, use a literal tab instead of t :

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

If you have input data tabs, you're out of luck and will have to generate a CSV file with a scripting language.

Do it manually with a real programming language

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

An example in Python.


Source link