CSV round trips: commas, quotes and embedded newlines
Use Python’s CSV reader and writer to round-trip commas, quotes and newlines while accounting for type loss and spreadsheet formula risks.
Why splitting on commas fails
A comma separates fields only when it is acting as a CSV delimiter. Inside a quoted field, it is ordinary text. Splitting a row on commas ignores that distinction: the example "Ada, Jr.",ok becomes three pieces instead of two. A CSV parser tracks quoting and returns the intended two fields. The standard library’s csv module is built for this format [1].
The code’s small split comparison demonstrates a specific failure, not a complete account of CSV dialects. Real files may vary in delimiter, quoting conventions and line endings. When a file’s format is known, configure the reader and writer to match it instead of assuming every CSV is identical.
Quoting and embedded newlines
A writer handles field values that contain commas or quotation marks, and quotes fields as needed. Embedded newlines are another reason a record is not necessarily one physical line: a quoted field can span lines. The example round-trips a Unicode name and a note containing a newline, along with commas and quotes, and asserts that the decoded rows equal the input rows.
Both StringIO objects use newline="", allowing the CSV module to handle newline processing itself. The writer explicitly uses CRLF as its record terminator. These choices make the in-memory test deterministic; they do not guarantee that a receiving application interprets every dialect the same way. For files, use the same newline setting when opening the file, and select an encoding appropriate for its content [1].
"""Python 3.10+; run this file to execute its round-trip tests."""
import csv
import io
import unittest
def encode(rows):
stream = io.StringIO(newline="")
csv.writer(stream, lineterminator="\r\n").writerows(rows)
return stream.getvalue()
def decode(text):
return list(csv.reader(io.StringIO(text, newline=""), strict=True))
class RoundTripTests(unittest.TestCase):
def test_round_trip(self):
rows = [["name", "note"], ["Ada, Jr.", 'says "hello"'],
["雪", "line one\nline two"], ["", ""]]
self.assertEqual(decode(encode(rows)), rows)
def test_split_is_not_a_parser(self):
self.assertEqual(len('"Ada, Jr.",ok'.split(",")), 3)
self.assertEqual(len(decode('"Ada, Jr.",ok')[0]), 2)
def test_none_needs_an_application_policy(self):
self.assertEqual(decode(encode([[None, ""]])), [["", ""]])
if __name__ == "__main__":
unittest.main()
CSV preserves fields, not application types
The successful round trip covers strings. CSV does not carry a general type system: a value such as 007 is text in the parsed result, and whether an application treats it as an identifier or a number is a separate decision. Likewise, the writer turns None into an empty field in this example. On reading, that empty field is indistinguishable from an originally empty string. The test demonstrates why applications that need nulls must define a representation or carry type information outside ordinary CSV fields.
The chosen output format also affects downstream software. Spreadsheet programs may infer types or apply their own parsing rules. A correct CSV parse establishes which fields were delimited and decoded; it does not preserve richer data semantics automatically.
Formula interpretation is a separate risk
CSV syntax correctness does not prevent spreadsheet formula injection. A field beginning with characters a spreadsheet interprets as a formula may be evaluated when a person opens the file. The round-trip functions do not inspect or neutralize such content, so they should not be treated as a security filter. If output is intended for spreadsheets, assess the target applications and apply a deliberate, documented policy for dangerous leading characters. That policy may change the data and should be kept separate from ordinary CSV quoting.
This matters because quoting a field makes it valid CSV but does not necessarily stop spreadsheet software from interpreting its contents. The test suite checks parsing behavior only; it makes no claim about spreadsheet execution or safety.
Run the local checks
Download the example as how-should-a-csv-file-handle-commas-in-text.py and run python3 how-should-a-csv-file-handle-commas-in-text.py. It uses the Python standard library only and requires Python 3.10 or newer. A successful run reports Ran 3 tests and OK.
That result confirms the three assertions in this local unittest suite: round-trip of the chosen values, the shown split failure, and None conversion. It does not validate other CSV dialects, a file’s encoding, spreadsheet behavior, or a production import pipeline.