MySQL escape line breaks exporting into CSV?

Question

I have a problem I have a "text" field with a bunch of "stories" that are pasted from word and such and as such include things like line breaks and tabs. I want to export to CSV maintaining these or be able to get them back.

But I can't figure out how to escape them so it works? When I export CSV I get a CSV with a bunch of single sentences on multiple rows/columns.

I tried replacing the news lines with other stuff but I kept messing up. Is there an easy way to do this without destroying data?

Answer 1

CSV is not a good format for arbitrary text, but you can try

SELECT ... INTO OUTFILE '/tmp/text.csv'
   FIELDS ESCAPED BY '""' TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\n' 
FROM yourtable

Answer 2

try something like this:

$line.=preg_replace("/\n\t/", ";", preg_replace("/,/", ";", $f['field5'])).',';

or

$line.=preg_replace("/\n/", ";", preg_replace("/,/", ";", $f['field5'])).',';

and in the end you have to display $line including header first.

$f['field5'] - data from db.

Answer 3

You could try a SELECT with a search and replace kind of thing, where you search for line breaks of '\n' and replace them with nothing ''. A SELECT won't actually replace anything in the table itself unless you did an UPDATE. See below query example.

(SELECT 'columnA', 'columnB', 'columnC')
UNION
(SELECT `columnA`, REPLACE(`columnB`, '\n', ''), `columnC`
FROM `yourtable`
INTO OUTFILE '/tmp/text.csv'
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"'
LINES TERMINATED BY '\n');