How to add a line break in a cell to a csv in python?

Question

In the CSV editor in Vscode it looks like this:

this is one cell

my code where I add the line break:

lista=''
for i in value:
            lista = lista + i + '\n'
stock_bodega.append(lista)

 result = pandas.DataFrame(list(zip(stock_bodega,inv_bodega)), columns =headers)
 result.to_csv(r''+funciones.RESULTS_PATH+FILE_NAME, header=True, index=False, sep=',', mode='w')

but if I open the csv with a text editor or excel it looks like this:

","0
0
0
","S
S
S

Excel:

expected output:

What is `value`? What is `inv_bodega`? Do you understand that `lista` is not a list? You are just producing a long, multi-line string. — Tim Roberts, Feb 21 '23 at 19:46
value is the values of dict, inv_bodega is a list with multiple strings, specifically lista,I understand that I am creating a multiline string, I thought it would work, until I saw in excel/csv that it is not so — Rodrigo Paredes, Feb 21 '23 at 19:50

Zach Young · Accepted Answer · 2023-02-22T21:53:56.237

The raw CSV would look something like:

Col_1,Col_2
"foo
Foo
FOO","1 I
2 II
3 III"
"bar
Bar
BAR","a A
b B
c C"
"baz
Baz
BAZ","4 $
5 %
6 ^"

When viewed as table, that will look something like:

+-------+-------+
| Col_1 | Col_2 |
+-------+-------+
| foo   | 1 I   |
| Foo   | 2 II  |
| FOO   | 3 III |
+-------+-------+
| bar   | a A   |
| Bar   | b B   |
| BAR   | c C   |
+-------+-------+
| baz   | 4 $   |
| Baz   | 5 %   |
| BAZ   | 6 ^   |
+-------+-------+

I can model this data in Python like:

my_data = [
    [ "Col_1"         , "Col_2"            ],
    [ "foo\nFoo\nFOO" , "1 I\n2 II\n3 III" ],
    [ "bar\nBar\nBAR" , "a A\nb B\nc C"    ],
    [ "baz\nBaz\nBAZ" , "4 $\n5 %\n6 ^"    ],

]

I don't use Pandas, so I'll show writing that data structure to a CSV with the csv module:

import csv

with open("output_rows.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerows(my_data)

Seeing your line:

result = pandas.DataFrame(list(zip(stock_bodega,inv_bodega)), columns=headers)

I believe you could hand this structure off to Pandas with ease, maybe:

result = pandas.DataFrame(my_data[1:], columns=my_data[0])

If I had a list of values that were supposed to end up joined together as a single multiline field in a column, like:

col1_src = [
    ["foo", "Foo", "FOO"],
    ["bar", "Bar", "BAR"],
    ["baz", "Baz", "BAZ"],
]

Then, I'd iterate those lists and join them with a newline, like:

col1_data = []
for line_values in col1_src:
    col1_data.append("\n".join(line_values))

print(col1_data)

[
    "foo\nFoo\nFOO",
    "bar\nBar\nBAR",
    "baz\nBaz\nBAZ",
]

and

col2_src = [
    ["1 I", "2 II", "3 III"],
    ["a A", "b B", "c C"],
    ["4 $", "5 %", "6 ^"],
]

col2_data = []
for line_values in col2_src:
    col2_data.append("\n".join(line_values))

[
    "1 I\n2 II\n3 III",
    "a A\nb B\nc C",
    "4 $\n5 %\n6 ^",
]

Which I could pass to the csv.writer() (and probably Pandas):

with open("output_cols.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["Col_1", "Col_2"])
    writer.writerows(list(zip(col1_data, col2_data)))

Update

Regarding the data not looking correct in Excel. I don't have Excel, but here's the data viewed in a few other spreadsheet apps:

App	Image
macOS Numbers
Google Sheets
Dropbox preview

Anyways, you can try changing the "\n" to "\r\n" for the line endings, though the CSV spec states either is acceptable (so Excel should also accept either).

Also, there's this thread, Importing CSV with line breaks in Excel.

I tried your solution, but when importing to excel I can't get the line break to appear, excel only lets me select the delimiter as a comma, I think I have to make another adjustment — Rodrigo Paredes, Feb 22 '23 at 12:40
@RodrigoParedes, added some screenshots of various spreadsheet apps (and previewer), and added a link about importing CSV w/linebreaks in Excel. — Zach Young, Feb 22 '23 at 21:55
The solution I found was to use the function `to_excel()` from Pandas, and with the file created, in excel I use the option adjust text and it is readable, the idea is that everything will be generated automatically, but this is enough for me, thanks for your answer — Rodrigo Paredes, Feb 23 '23 at 12:55

How to add a line break in a cell to a csv in python?

1 Answers1