Postgresql: CSV export with escaped linebreaks

Question

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)?

Community · Accepted Answer · 2021-10-07 05:54:43Z

5

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.

CommunityBot

11 silver badge

answered Aug 29, 2013 at 14:22

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. You are right. The problem was not the CSV-export but the import in Excel. Using libreoffice the file can be imported.