• Outputting SQL Results to CSV

    I recently came across an issue where I wanted to output the results of a (rather large) SQL query to a CSV file. Historically, I have been copying the DB from a backup to my local machine, but as it has grown larger, it has become increasingly difficult to do (as a result of the time it takes to download, unzip and install).

    As a result, I have been looking into the possible ways in which the query might be executed on the live database (it’s only a select, so no danger of changing code) but still getting the data back in a usable form. So far, I have come up with two possibilities.

    Outfile:

    Some additional lines can be added into your query which will output a file at the location you have specified:

    SELECT order_id,product_name,qty
    FROM orders
    INTO OUTFILE '/tmp/orders.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

    This is a suggestion that I saw on StackOverflow. Unfortunately, the server I wish to use is an RDS instance, and to the above will not work (/tmp/ cannot be written to).

    Redirecting Output

    The alternative method I have used is to output a tab-delimited file rather than a CSV. This can simply be done by redirecting the output of the command – e.g:

    mysql -u<username> -p<password> -h <host> db_name < db_query.sql > /tmp/orders.csv

Leave a Reply

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

Are you human: *