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:

Answer 1

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.