CSV/TSV reading silently drops carriage returns inside quoted fields #895

rows_from_file() reads CSV/TSV through a text wrapper opened in universal-newline mode, so a carriage return inside a quoted field is silently rewritten to a line feed before csv ever sees it.

Reproduction

Python API:

import io
from sqlite_utils.utils import Format, rows_from_file

rows, _ = rows_from_file(io.BytesIO(b'id,v\r\n9,"a\r\nb"\r\n'), format=Format.CSV)
print(dict(next(iter(rows))))
# current:  {'id': '9', 'v': 'a\nb'}      <- the CR is gone
# expected: {'id': '9', 'v': 'a\r\nb'}

The same happens through the CLI, so it is not just a library-level issue:

$ sqlite-utils rows t.db t -c id -c v --csv | od -c
0000000    i   d   ,   v  \r  \n   9   ,   "   a  \r  \n   b   "  \r  \n

sqlite-utils insert new.db t out.csv --csv --pk id round-trips "a\r\nb" as "a\nb", so a rows --csv piped into insert --csv loses the byte with no warning.

The --nl format is unaffected, because JSON escapes control characters — which is why the formats disagree about the same data:

$ sqlite-utils rows t.db t --nl      # keeps "a\r\nb"

Cause

sqlite_utils/utils.py:350:

decoded_fp = io.TextIOWrapper(fp, encoding=use_encoding)

io.TextIOWrapper defaults to newline=None, which enables universal newlines: \r\n and lone \r are translated to \n as the stream is read. That happens underneath csv, so by the time csv.DictReader gets the text the carriage return has already been destroyed. The csv module documents the opposite requirement — "If csvfile is a file object, it should be opened with newline=''" — precisely so that newlines inside quoted fields survive.

The writer side is fine: csv.writer(sys.stdout, ...) emits the field intact. It is only the read path that is affected, so the loss is easy to miss — the file on disk looks correct.

Suggested fix

decoded_fp = io.TextIOWrapper(fp, encoding=use_encoding, newline="")

I confirmed this is the whole change on this input:

current:    {'id': '9', 'v': 'a\nb'}
newline='': {'id': '9', 'v': 'a\r\nb'}

This also makes rows_from_file() agree with the documented csv behaviour for TSV and for the --delimiter/--quotechar variants that funnel through the same helper.

Happy to send a PR with a regression test if that is useful — I did not want to open one unasked, but I can say the fix is a single keyword argument.