Postgresql: CSV export with escaped linebreaks
csv, export-to-csv, postgresql
Solution
Line breaks are supported in CSV if the fields that contain them are enclosed in double quotes.
So if you had this in the middle of the file:
just another value;f*** value;"value with
newline"
it will be taken as 1 line of data spread on 2 lines with 3 fields and just work.
On the other hand, without the double quotes, it's an invalid CSV file (when it advertises 3 fields).
Although there's no formal specification for the CSV format, you may look at RFC 4180 for the rules that generally apply.
Problem
I exported some data from a postgresql database using (all) the instruction(s) posted here: Save PL/pgSQL output from PostgreSQL to a CSV file But some exported fields contains newlines (linebreaks), so I got a CSV file like: ``` header1;header2;header3 foobar;some value;other value just another value;f*** value;value with newline nextvalue;nextvalue2;nextvalue3 ``` How can I escape (or ignore) theese newline character(s)?