Line breaks within a csv field

how0909 · October 14, 2021, 1:21pm

Hi,
I would like to generate a .csv file from a json file using php or whatever intended to be opened by calc that would have newlines within a field.
I was expecting that there would be an escape sequence for that but have had no success so far except to actually have line breaks within a quoted string but this is hard to read if you are editing the csv.
Here is an example:

Bike;$19;"Red
Green
Blue"
Chair;$10;"Wood 
Metal
Plastic"
Chair;$10;'Wood\u000aMetal\nPlastic'

The last line has some escape sequences I have tried.
The first two work, but are not easy to pick out for editing.
I saw the hexdecimal sequence suggested for use in a Base Form but it does not work here.

erAck · October 14, 2021, 1:57pm

The only correct form is to have embedded line breaks in a quoted field, like in the first two records. Note that in case a field contains a quotation mark character the field must be quoted as well and that quotation mark character needs to be doubled to escape it, like "this ""field"" content"
See RFC 4180.

Wanderer · October 14, 2021, 2:36pm

If you prefer the quoted \n you may import firs, then use search&replace to exchange \n with chr(10).
The problems begin, if you allow quoted backslash like \n or there are suddenly some network-shares like \netdrive and \newdisk

So be careful

how0909 · October 18, 2021, 2:26am

erAck’s solution works and I can just get used to it.
But I can’t seem to get Wanderer’s suggestion to work.
Inserting a chr(10) in a line doesn’t create a linebreak.
For example using the same input as above and using Edit > Find.
If I enter \\n it is found in 3C:)
If I replace those found with chr(10) then chr(10) is what displays, not a newline.
What am I doing wrong? Do I need to concatenate chr(10) into that line somehow?

Wanderer · October 18, 2021, 5:13am

If you have the imported text containing \n in A1 use for english locale:

=SUBSTTUTE(A1;"\n";CHAR(10))

I used chr(10) to express you have to use the exact ASCII-Character.
It depends on the environment you are using, how to encode this.

how0909 · October 18, 2021, 2:40pm

Well I would need to do the whole sheet.
It would be easiest to use the Edit > Find/Replace?
What could I use for CHAR(10) as the Replace to produce a newline in the cell?
Is there an escape sequence to use in that situation?

flywire · October 19, 2021, 10:01am

Do some testing with Excel if you can as it supports <shift>+<enter> within a cell. It would be good to make it compatible. (Don’t be surprised if it’s just stripped from a csv file.)

erAck · October 19, 2021, 2:22pm

@flywire you might be surprised to learn that LibreOffice Calc supports Ctrl+Enter in a cell input to create a new line. (because Shift+Enter closes the cell input and moves into the opposite direction than Enter).