Read a CSV file

A CSV cell that contains a line break comes back altered, and the cause is the way the file was opened.

import csv

with open("data.csv", "w", newline="") as f:
    f.write('name,note\r\nada,"one\r\ntwo"\r\n')

with open("data.csv", newline="") as f:
    print(list(csv.reader(f)))
with open("data.csv") as f:
    print(list(csv.reader(f)))
with open("data.csv", newline="") as f:
    for row in csv.DictReader(f):
        print(row["name"], "->", repr(row["note"]))

[['name', 'note'], ['ada', 'one\r\ntwo']]
[['name', 'note'], ['ada', 'one\ntwo']]
ada -> 'one\r\ntwo'

Text mode translates line endings on read by default, so the carriage return and line feed pair inside the quoted cell becomes a single line feed before the csv module sees it. Opening with newline="" turns the translation off and lets the parser handle line breaks itself, which keeps the cell exactly as written. This is required by the csv documentation. DictReader uses the header row for the keys, and a row longer than the header collects the extras under a None key.

Add encoding="utf-8", or utf-8-sig for files saved by Excel, which begin with a byte order mark that would otherwise end up in the first column name. Every value is a string, so convert numbers yourself. The same newline="" rule applies to csv.writer, which would otherwise write blank lines between rows on Windows.