Dumping MySQL query results out to a CSV



Published: 2018-07-13 08:45:49 +0000
Categories: MySQL,

Language

MySQL

Description

Sometimes, you want to query a database and dump the result out to a CSV so that you can process the results with simple text tools like awk and sed.

This post shows you how to tell MySQL to write the results of a query out to a named file in CSV format, as well as how to deal with an increasingly common error response when secure files is enabled

Snippet

SELECT fields FROM table
[WHERE filter]
INTO OUTFILE 'filepath'
FIELDS TERMINATED BY 'delimiter'
ENCLOSED BY 'enclosure'
LINES TERMINTED BY 'EOL'

Usage Example

SELECT id,name,address FROM people
WHERE active=1
INTO OUTFILE '/var/lib/mysql-files/people.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINTED    BY '\n'

-- Or for the same query, but a tab delimited CSV

SELECT id,name,address FROM people
WHERE active=1
INTO OUTFILE '/var/lib/mysql-files/people.csv'
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINTED BY '\n'

-- If you receive the error message
-- The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
-- It means that you're trying to write the CSV file into a directory that MySQL isn't configured
-- to write into. You can check valid directories with one of the following commands (depending on MySQL version)

SHOW VARIABLES LIKE "secure_file_priv";
SELECT @@global.secure_file_priv;

License

BSD-3-Clause

Keywords

Mysql, CSV, secure_file_priv, output, Comma Seperated,

Latest Posts

Remotely backing up PFsense Configuration (BASH)
FFMPEG: Converting RMVB to X264 MP4 (BASH)
Recursively print table (print_r equivalent) (LUA)
Bulk Delete Comments from (Self-Hosted) JIRA Issues (Misc)
Add a static entry to the ARP table (BASH)
SSL Cipher Hex codes to Human Readable Names (Misc)
Convert Ascii to Binary (BASH)
Intercepting Outbound DNS Queries (BASH)
Handle Google Verification files within NGinx Configuration (NGinx)
Getting WhatsApp Rich Snippet Previews Working (Misc)

Copyright © 2018 Ben Tasker | Sitemap | Privacy Policy
Available at snippets.bentasker.co.uk and snippets.6zdgh5a5e6zpchdz.onion