MySQL Bugs: #1631: CSV output

Bug #1631	CSV output
Submitted:	22 Oct 2003 14:25	Modified:	7 Dec 2006 9:33
Reporter:	Robert Graf	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	3.x,4.x	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

is there any feature to SELECT INTO (CSV) FILE ?

Please note, a "real" .csv (comma separated) is not a field/line separated file with escaped(!!!!) characters.

If a table includes a field of the type 'text' and I SELECT INTO FILE, all \r & \n and other special characters will be escaped. That's not that what is in a .csv file (save a file from MS EXCEL as .csv file!)

In a .csv fields are separated by a field separater (normally ";") and line terminated by a (normally "\n" or "\r\n" ...).

If there is a control, field, line or quote character in a value, the field will be quoted with (normally '"').

And if there is the quote character itself in the value, it will be double-quoted!

This means a control character will NOT escaped!

Currently you can not produce this:
Column1;;"Colu;mn3";"Colu""mn4";"Colu
mn5"

=> 'mn5' is a part of the previous line! There is a (not escaped!) "\n".

You would produce:
"Column1";\N;"Colu;mn3";"Colu\"mn4";"Colu\
mn5"

For a .csv file you should NOT escape the characters only double-quote the quotes.

Also you should not produce somehting like ';\N;' or ';NULL;' ..
In a .csv I need ';;'!

So currently we have
FIELD TERMINATED BY
ESCAPED BY
LINES TERMINATED BY
OPTIONALLY ENCLOSED BY

What we need is (or something like that)
ESCAPED BY NULL    ( to produce no escaping).
- also the others should work with NULL. "''" does not work(?).

DOUBLE-QUOTE WITH '"'   (new for double-quote). Each charachter " will result in "".

PRINT NULLS AS ... default "\N" ... or for csv .. PRINT NULLS AS NULL => ;;

Any questions?
Robert GRAF

How to repeat:
There is nothing to repeat - it's a feature request!

Suggested fix:
There is nothing for a suggested fix - it's a feature request!

I would think that instead of having the CSV option specified in the query itself it could be a command-line option for mysql.  We already have -H for html output, and -X for xml output, just add another for CSV file format.

I think this one is a duplicate :
http://bugs.mysql.com/bug.php?id=4058

This already has been implemented. Use --fields-terminated-by option.

See also http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html, http://dev.mysql.com/doc/refman/4.1/en/mysqlimport.html and http://dev.mysql.com/doc/refman/4.1/en/load-data.html