Passing \N for Null values into CSV

darkdestroyer

Hi All,

I am outputting results of a simple query to CSV.
However whenever it comes across a field that has a null value is shows \N in the field in the CSV file.
Is there anything that can be done to simply leave the field blank.

Here is my code to output to CSV:

into outfile '/CSV/$filename.csv' fields terminated by ',' enclosed by '\"' lines terminated by '\r\n'

The resulting output will show something like:
[ATTACH]4717[/ATTACH]

I just need to be able to show a blank field.

Thanks,
Paul

output.jpg

Derokorian

The only way I know of is to modify your select statement to go from SELECT column1, column2 (, etc...) FROM... to SELECT IFNULL(column1,''), IFNULL(column2,'') (, etc...) FROM..

NogDog

What happens if you add...

FIELDS ESCAPED BY '\\'

...?

darkdestroyer

The IFNULL works a treat. I couldnt get FIELDS ESCAPED BY '\' to work, the script kept failing.

One thing, on the IFNULL, it works fine if you have just the column name, but if you changing the format, in a date for example:
DATE_FORMAT(DOB, '%d/%m/%Y') AS DOB
I tried:
IFNULL(DATE_FORMAT(DOB, '%d/%m/%Y') AS DOB,'')

but this doesnt work, anyone any suggestions on using IFNULL when using formatting options.

Thanks for the suggestions.

Cheers.

Derokorian

This:

IFNULL(DATE_FORMAT(DOB, '%d/%m/%Y') AS DOB,'')

was almost right except that the AS DOB would come after the ifnull like so:

IFNULL(DATE_FORMAT(DOB, '%d/%m/%Y'),'') AS DOB