Adding a newline character within a cell (CSV)

Question

I would like to import product descriptions that need to be logically broken according by things like description, dimensions, finishes etc. How can I insert a line break so that when I import the file they will show up?

Or this: http://stackoverflow.com/questions/566052/can-you-encode-cr-lf-in-into-csv-files . — rajah9, Jun 03 '14 at 15:51
Could you clarify what Excel and CSV have to do with your question? Are you writing CSV in a text editor and targeting Excel's CSV import? — binki, Jun 02 '16 at 13:54

score 50 · Answer 1 · edited May 08 '15 at 16:33

50

I struggled with this as well but heres the solution. If you add " before and at the end of the csv string you are trying to display, it will consolidate them into 1 cell while honoring new line.

csvString += "\""+"Date Generated: \n" ; 
csvString += "Doctor: " + "\n"+"\"" + "\n";

edited May 08 '15 at 16:33

Luís Cruz

14,780
16
68
100

answered May 08 '15 at 16:11

Dan Robidoux

509
4
2

This is really helpful. Thanks :) – Sarthak Sharma Aug 26 '20 at 05:26
2

If the " shows up and the line-breaks still cause trouble, look for spaces! There should be no space between the coma (,) ending the previous cell and the " starting the multiline text. – Samuel Åslund Sep 24 '20 at 14:34

score 49 · Accepted Answer · edited May 23 '17 at 12:18

49

This question was answered well at Can you encode CR/LF in into CSV files?.

Consider also reverse engineering multiple lines in Excel. To embed a newline in an Excel cell, press Alt+Enter. Then save the file as a .csv. You'll see that the double-quotes start on one line and each new line in the file is considered an embedded newline in the cell.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 03 '14 at 16:35

rajah9

11,645
5
44
57

11

Unfortunately, this file cannot be imported correctly by Excel. Excel will import them as multiple cells (at least, for Mac version). – Tom Yeh Feb 04 '20 at 08:23
The Mac uses a different combination to enter a line break. This site: https://apple.stackexchange.com/questions/84609/how-to-add-a-line-break-in-a-cell-in-excel-for-mac says "The answer is to use Control+Option+Enter, which will create a line break in the cell." – rajah9 Feb 04 '20 at 13:27
5

Sorry, I probably didn't make it clear. I didn't mean how to enter a line break in Excel. I mean if you export a CSV with a cell containing line break and then import the file back, Excel cannot handle it correctly. Here is [some workaround](https://superuser.com/questions/319549/importing-multiline-cells-from-csv-file-into-excel/1494059#1494059) – Tom Yeh Feb 05 '20 at 02:08

score 2 · Answer 3 · answered Oct 19 '18 at 12:58

2

On Excel for Mac 2011, the newline had to be a \r instead of an \n

So

"\"first line\rsecond line\""

would show up as a cell with 2 lines

answered Oct 19 '18 at 12:58

Sam

5,375
2
45
54

1

What about combination of `"\n\r"`? It seems to work in macOS, but not sure if it works on Windows. – elquimista May 15 '20 at 19:53
1

this did not work on windows Office 2019 both as "\n\r" or as "\"first line\rsecond line\"" or any combination of \n and \r. – Raja Nagendra Kumar Jun 22 '20 at 01:01

score 2 · Answer 4 · answered Dec 30 '18 at 05:41

2

I have the same issue, when I try to export the content of email to csv and still keep it break line when importing to excel.

I export the conent as this: ="Line 1"&CHAR(10)&"Line 2"

When I import it to excel(google), excel understand it as string. It still not break new line.

We need to trigger excel to treat it as formula by: Format -> Number | Scientific.

This is not the good way but it resolve my issue.

answered Dec 30 '18 at 05:41

Luan Nguyen

186
1
5

Windows Office 2019 reports a warning to accept this formula, however once forced to accept it, it does work. – Raja Nagendra Kumar Jun 22 '20 at 01:03
Unfortunatelly google sheets's script parseCsv says: "Exception: Could not parse text" – Fanky Sep 29 '21 at 07:42

score 2 · Answer 5 · answered Mar 31 '21 at 22:21

2

supposing you have a text variable containing:

const text = 'wonderful text with \n newline'

the newline in the csv file is correctly interpreted having enclosed the string with double quotes and spaces

'" ' + text + ' "'

answered Mar 31 '21 at 22:21

Jonathan

595
5
10

score 0 · Answer 6 · answered Jun 13 '17 at 13:12

I was concatenating the variable and adding multiple items in same row. so below code work for me. "\n" new line code is mandatory to add first and last of each line if you will add it on last only it will append last 1-2 character to new lines.

  $itemCode =  '';
foreach($returnData['repairdetail'] as $checkkey=>$repairDetailData){

    if($checkkey >0){
        $itemCode   .= "\n".trim(@$repairDetailData['ItemMaster']->Item_Code)."\n";
    }else{
        $itemCode   .= "\n".trim(@$repairDetailData['ItemMaster']->Item_Code)."\n";             
    }
    $repairDetaile[]= array(
        $itemCode,
    )
}
// pass all array to here 
foreach ($repairDetaile as $csvData) { 
    fputcsv($csv_file,$csvData,',','"');
}
fclose($csv_file);

score 0 · Answer 7 · answered Aug 03 '22 at 16:49

I converted a pandas DataFrame to a csv string using DataFrame.to_csv() and then I looked at the results. It included \r\n as the end of line character(s). I suggest inserting these into your csv string as your row separation.

Depending on the tools used to generate the csv string you may need escape the \ character (\r\n).

Adding a newline character within a cell (CSV)

7 Answers7

Linked