GoAdmio

csv

Export the result of a SELECT query from MySQL/MariaDB to a CSV file

On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also


Overview

/go/csv runs a SELECT query on the server's MariaDB/MySQL database and saves the result as a CSV file. You can export a whole table, chosen columns, or a filtered result, from any database on the server.

The query runs with the database root account, so it can read every database.


Quick start

# Export a whole table to /root/orders.csv
/go/csv -s "SELECT * FROM shop.orders" -o /root/orders.csv

[!WARNING] The export file contains your data in plain text and can be large. Store it somewhere safe and delete it when you no longer need it. The query is also saved in /var/log/go/csv.log, so don't put passwords or other secrets in it.


Syntax

/go/csv -s "<SELECT query>" -o <output file>

Run it as root. Put the query in double quotes and use single quotes for text values inside it.


Options

Option Required Notes
-s <query> Yes A single SELECT statement, without a trailing semicolon. Name the database in the query (database.table).
-o <file> Yes The file to create. A relative path is relative to the current directory. An existing file is overwritten. Don't use spaces in the name.

If you pass an unknown option, the command prints Usage: csv [-s] SQL Query [-o] Output File Name, but it keeps going with the values it has. Always give both options.


Output

The command prints nothing when it works. Errors from the database are shown on screen.

Every run is logged with its arguments and a timestamp, for example [Nov 20 09:15:02]# csv -s SELECT * FROM shop.orders -o /root/orders.csv, to /var/log/go/csv.log.

File format

Setting Value
Separator Comma.
Quoting Every value is wrapped in double quotes, numbers included.
Line ending A new line (\n).
Header row None. The file contains only data rows.
Special characters Escaped with a backslash. Quotes inside a value are written as \", not doubled.
Empty values NULL is written as \N.

Examples

# Export rows that match a condition (single quotes inside the query)
/go/csv -s "SELECT * FROM crm.DLD WHERE Mobile LIKE '20%' OR Keywords LIKE '%egypt%' GROUP BY Mobile" -o egypt.csv
# Export two columns
/go/csv -s "SELECT id, email FROM shop.users" -o /root/users.csv
# Add a header row by putting it first with UNION ALL
/go/csv -s "SELECT 'id','email' UNION ALL SELECT id, email FROM shop.users" -o /root/users.csv
# Check the result
head /root/users.csv
wc -l /root/users.csv

Troubleshooting

Symptom Likely cause Fix
The usage line, followed by a database syntax error An option is missing or unknown. Pass -s with the query and -o with the file.
ERROR 1064 ... You have an error in your SQL syntax The query ends with ;, or it isn't a SELECT. Remove the semicolon. The command adds its own INTO OUTFILE clause to the end of your query.
ERROR 1146 ... Table ... doesn't exist No database name in the query. Write database.table.
ERROR 1290 ... running with the --secure-file-priv option The database server only allows exports to a certain folder. Set secure_file_priv to an empty value in the MariaDB configuration, then restart MariaDB.
ERROR 1045 ... Access denied The root database account can't log in without a password from this user. Run the command as root on a server where sudo mariadb works.
mv: missing destination file operand No -o was given. Run again with -o. The unfinished file is deleted by the next run.
No output file appears, and there is no error The database's data folder isn't /var/lib/mysql, where the command looks for the file. Export on a server that uses the default folder, or use mariadb directly.
The file isn't where you expected A relative -o path is relative to the current directory. Use a full path such as /root/file.csv.
An empty file The query returned no rows. Check the query.
Excel shows the quotes or \N oddly, or non-English text is garbled The file uses backslash escapes and has no UTF-8 marker. Import it into Excel as a UTF-8, comma-separated file.
The export is slow, or the disk fills up The file is first written inside the database's data folder. Free space, or export fewer rows or columns.

Details

How it works

  1. Reads the -s and -o options.
  2. Deletes any file named file.csv inside /var/lib/mysql.
  3. Runs <query> INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' with sudo mariadb. The database server writes the file itself, on the server, into its own data folder.
  4. Finds the new file.csv under /var/lib/mysql.
  5. Moves it to the file name you gave with -o.

The query must be one SELECT statement without a trailing semicolon, because the export clause is added directly after it.

Requirements

Notes


See also

Command Description
import Import a SQL dump into a MySQL/MariaDB database
table Import a single table from a dump file in MySQL/MariaDB
sql Create a MySQL database and import an SQL file into it
dbls List all databases and their sizes
collectmail Collect every email address stored in an email column of any MySQL/MariaDB database into one file
copytable Copy a MySQL/MariaDB table from one server to another