How do I handle NULL values in a mysql SELECT ... OUTFILE statement in conjunction with FIELDS ESCAPED BY? NULL values are currently being truncated

Question

I'm encountering some difficulties using MySQL's SELECT ... OUTFILE on result sets that include both null values and columns that require double quote escaping (ie, columns that contain '"' characters). This is the outfile syntax I am using:

INTO OUTFILE '$csv_file' 
FIELDS ESCAPED BY '""' TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

My problem is concerning the FIELDS ESCAPED BY portion of the query - if this portion is omitted, then null values will export properly (...,"\N",... is what it looks like in the csv).

However, columns that contain double quotes will get split across multiple lines/columns in excel. This is because excel requires that '"' characters inside columns to be escaped by writing them as '""'.

Including the FIELDS ESCAPED BY clause fixes the excel problem with columns containing double quote characters, however, it breaks NULL columns. NULL columns get exported as ( ..."N,... ) missing both the backslash and the trailing quotation mark on the column. In excel, this causes multiple columns to collapse into each other due to the lack of a closing quotation.

My goal is to be able to export columns that contain double quotes and newlines, as well as export null columns as \N, however I can't seem to figure out how to do it. MySQL docs state that FIELDS ESCAPED BY affects how NULL columns are outputted, but I can't figure out how an escape sequence of '""' results in dropping the backslash and the trailing quote on a NULL column

Currently, my solution is to perform a string replace on each line as I output it to the user, by using FIELDS ESCAPED BY and replacing '"N,' with '"\N",'. This seems to work, but it doesn't feel right, and I'm afraid of it causing some sort of issues down the line

IFNULL( ) on the select columns is potentially an option, but the way we are using this in our code, is actually quite difficult to implement. It also needs to be done for each column that could potentially have NULL values, so it's a solution I'd like to avoid if I can

Thanks!

Answer 1

I was able to successfully save MySQL query results as CSV and import them into Excel as follows:

1. Use the form...

   IFNULL(ColumnA, "" ) AS "Column A",
   

...for each column or expression in your SELECT statement than can possibly return a NULL (\N). This will ensure NULL values in your CSV file appear as properly quoted empty strings rather than improperly quoted \N's. Instead of an empty string, you could possibly specify a value to represent a NULL, e.g...

    IFNULL(ColumnA, "~NULL~" ) AS "Column A",

2. Use the following OUTFILE options:

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'

Note that ESCAPED BY specifies one double quote, as does ENCLOSED BY. I haven't tested whether OPTIONALLY ENCLOSED BY will be successful, so I just leave OPTIONALLY out.

Using a double-quote to escape another double-quote within a quoted field value is required per the CSV specification - RFC 4180, section 2.7.