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!

Costrel

By any chance do you happen to remember the solution / workaround you used to remove \Ns from null values while performing SELECT * INTO OUTFILE..? – Secor

@Secor This was many years ago now, but our solution was simply to a string replace (see my text above for the specific replacements). We were using php at the time, so we just did a replace then served the resulting replacement with content-type text/csv headers. It worked for many years (and presumably still works... though I no longer work at the company where this was required) – Costrel

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.

Phillipp

It works when change the ESCAPE BY "" ( double quotes ) – Akela

In an integer column with some values null, the IFNULL function quotes all integers as "123" and nulls as "~NULL~" – Levis

Answer 2

Try to use coalesce function to convert the column that can be null to "" http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

Poachy

Thanks for the tip, but unfortunately this has the same drawback as IFNULL in that I have to specify each column that could potentially have NULL values. Implementation-wise IFNULL and COALESCE are pretty much the same, though I do wonder which would have better performance on a very large outfile export. – Costrel