Python Doc & Data Automation

Export CSV with a Custom Delimiter and Quoting

The bank's upload portal rejects the payment file with Line 42: unexpected number of fields, the German ERP shows every row in one column, and a partner's importer reads "ACME, Ltd" as two fields. The export is a one-liner and every recipient wants something different: semicolons because their Excel uses a comma as the decimal separator, pipes because their data contains both commas and semicolons, quotes around everything, quotes around nothing, CRLF line endings, Latin-1 encoding.

Root Cause

"CSV" is not one format. RFC 4180 describes comma separation with double-quote escaping, but real recipients define their own dialect: the field delimiter, the quote character, whether quoting is minimal or applied to every field, how a quote inside a quoted field is escaped (doubled, or preceded by a backslash), the line terminator, and the character encoding. Regional settings compound it — in locales where the decimal separator is a comma, Excel writes and expects semicolon-separated files, and a numeric column exported as 1240.50 is read as text. pandas' defaults implement one dialect (comma, minimal quoting, doubled quotes, \n, UTF-8), so any mismatch shows up as parse errors or misaligned columns at the recipient, not at export time.

Minimal Diagnostic

Inspect a sample file the recipient accepts — or one they sent you — and read its dialect off the bytes rather than guessing.

# stdlib only
import csv
from pathlib import Path

SAMPLE = Path("in/recipient-sample.csv")

def sniff(path: Path, sample_bytes: int = 64_000) -> dict:
    raw = path.read_bytes()[:sample_bytes]
    encoding = "utf-8-sig" if raw.startswith(b"\xef\xbb\xbf") else "utf-8"
    try:
        text = raw.decode(encoding)
    except UnicodeDecodeError:
        encoding = "cp1252"
        text = raw.decode(encoding, errors="replace")
    dialect = csv.Sniffer().sniff(text[:8000])
    crlf = raw.count(b"\r\n")
    lf_only = raw.count(b"\n") - crlf
    quoted_fields = text.count('"')
    return {
        "encoding": encoding,
        "delimiter": repr(dialect.delimiter),
        "quotechar": repr(dialect.quotechar),
        "doublequote": dialect.doublequote,
        "escapechar": repr(dialect.escapechar),
        "line_terminator": "CRLF" if crlf and not lf_only else "LF" if lf_only and not crlf else "mixed",
        "quote_characters": quoted_fields,
        "first_line": text.splitlines()[0][:80],
    }

if __name__ == "__main__":
    for key, value in sniff(SAMPLE).items():
        print(f"{key:>17}: {value}")

         encoding: cp1252
        delimiter: ';'
        quotechar: '"'
      doublequote: True
       escapechar: None
  line_terminator: CRLF
 quote_characters: 0
       first_line: Kundennummer;Name;Betrag;Buchungsdatum

Semicolons, Windows line endings, Latin-1-family encoding and no quoting at all — a common European ERP dialect. The absence of quote characters is itself information: their importer may not handle quoted fields, so values containing a semicolon must be cleaned rather than quoted.

The parts of a CSV dialect The delimiter is set with sep and is commonly a comma, semicolon, pipe or tab; a mismatch puts every row in one column. The quote character is set with quotechar and is usually a double quote; a mismatch splits fields that contain the delimiter. The quoting policy is set with quoting and ranges from minimal to all; too little quoting breaks fields containing delimiters. The line terminator is set with lineterminator and is LF or CRLF; a mismatch can produce blank rows. The encoding is set with encoding and mismatches corrupt accented characters. Dialect part pandas argument Common values Mismatch causes Delimiter sep ',' ';' '|' tab one column per row Quote char quotechar " or ' split fields Quoting policy quoting MINIMAL, ALL, NONE field count errors Line terminator lineterminator LF or CRLF blank rows Encoding encoding utf-8, utf-8-sig, cp1252 mojibake

Fix: Declare the Dialect Once and Export Through It

Keep each recipient's dialect in one place, including number and date formatting, and write exports through a single function. Changed lines carry comments.

# pip install "pandas>=2.2"
import csv
from dataclasses import dataclass, field
from pathlib import Path
import pandas as pd

@dataclass(frozen=True)
class Dialect:
    sep: str = ","
    quoting: int = csv.QUOTE_MINIMAL
    quotechar: str = '"'
    doublequote: bool = True
    escapechar: str | None = None
    lineterminator: str = "\r\n"
    encoding: str = "utf-8"
    decimal: str = "."
    date_format: str = "%Y-%m-%d"
    na_rep: str = ""
    strip_delimiters_in_values: bool = False       # for importers that cannot read quoted fields

RECIPIENTS = {                                      # changed: one entry per partner, versioned with the code
    "bank-upload": Dialect(sep=",", quoting=csv.QUOTE_ALL, encoding="utf-8", date_format="%d/%m/%Y"),
    "erp-de": Dialect(sep=";", quoting=csv.QUOTE_NONE, encoding="cp1252", decimal=",",
                      date_format="%d.%m.%Y", strip_delimiters_in_values=True),
    "partner-pipe": Dialect(sep="|", quoting=csv.QUOTE_MINIMAL, escapechar="\\", doublequote=False),
}

def export_csv(frame: pd.DataFrame, dest: Path, recipient: str) -> Path:
    d = RECIPIENTS[recipient]
    out = frame.copy()
    if d.strip_delimiters_in_values:                                   # changed: unquotable importer
        for col in out.select_dtypes(include=["object", "string"]).columns:
            out[col] = out[col].astype("string").str.replace(d.sep, " ", regex=False) \
                                                .str.replace(r"[\r\n]+", " ", regex=True)
    dest.parent.mkdir(parents=True, exist_ok=True)
    out.to_csv(
        dest, index=False,
        sep=d.sep, quoting=d.quoting, quotechar=d.quotechar,           # changed: dialect, not defaults
        doublequote=d.doublequote, escapechar=d.escapechar,
        lineterminator=d.lineterminator, encoding=d.encoding,
        decimal=d.decimal, date_format=d.date_format, na_rep=d.na_rep,
    )
    return dest

if __name__ == "__main__":
    payments = pd.DataFrame({"customer": ["ACME, Ltd", "Müller GmbH"],
                             "amount": [1240.5, 98.0],
                             "booked": pd.to_datetime(["2026-09-01", "2026-09-02"])})
    print(export_csv(payments, Path("out/erp-de.csv"), "erp-de").read_text(encoding="cp1252"))

customer;amount;booked
ACME  Ltd;1240,5;01.09.2026
Müller GmbH;98,0;02.09.2026

QUOTE_NONE raises Error: need to escape, but no escapechar set if a value still contains the delimiter, which is why the dialect strips delimiters from values for that recipient — an explicit, visible loss instead of a corrupt file. decimal="," affects only float formatting, not the delimiter, so semicolon separation and comma decimals coexist correctly.

The same rows in two dialects With defaults, the file uses comma separation, a dot decimal separator, ISO dates and quotes the customer value containing a comma. With the German ERP dialect, the file uses semicolon separation, comma decimals, day dot month dot year dates, cp1252 encoding and no quoting, with the comma in the customer name replaced by a space because the importer cannot read quoted fields. Default dialect customer,amount,booked "ACME, Ltd",1240.5,2026-09-01 Müller GmbH,98.0,2026-09-02 UTF-8, LF erp-de dialect customer;amount;booked ACME Ltd;1240,5;01.09.2026 Müller GmbH;98,0;02.09.2026 cp1252, CRLF, no quotes

Variant Fix 1: Values That Contain the Delimiter, Quotes or Line Breaks

When the recipient does support quoting, the safe default is minimal quoting with doubled quotes — the RFC 4180 behaviour that Excel and most importers implement. Verify the round trip for the awkward values rather than assuming:

# pip install "pandas>=2.2"
import csv
import io
import pandas as pd

TRICKY = pd.DataFrame({
    "text": ['ACME, Ltd', 'He said "hello"', "line one\nline two", "trailing space ", ";semicolon"],
    "amount": [1.5, 2.0, 3.25, 4.0, 5.5],
})

def roundtrip(frame: pd.DataFrame, **kwargs) -> bool:
    buf = io.StringIO()
    frame.to_csv(buf, index=False, **kwargs)
    read_kwargs = {k: v for k, v in kwargs.items() if k in
                   {"sep", "quotechar", "escapechar", "doublequote", "encoding", "decimal"}}
    back = pd.read_csv(io.StringIO(buf.getvalue()), dtype={"text": "string"}, **read_kwargs)
    return back["text"].tolist() == frame["text"].tolist()

for name, opts in {
    "minimal": dict(quoting=csv.QUOTE_MINIMAL),
    "all": dict(quoting=csv.QUOTE_ALL),
    "backslash-escape": dict(quoting=csv.QUOTE_MINIMAL, doublequote=False, escapechar="\\"),
}.items():
    print(f"{name:<18} round-trips: {roundtrip(TRICKY, **opts)}")

Embedded line breaks are the value type that most often breaks a recipient's importer even when quoting is correct, because many simple parsers split on newlines before handling quotes. If a partner's system fails on multi-line fields, replace line breaks with a marker on export and restore them on import — and record that decision in the dialect, where the next person will find it.

Variant Fix 2: Fixed-Width and Tab-Separated Variants

Bank and payroll systems sometimes require tab separation or fixed-width records rather than delimited ones:

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

def export_tsv(frame: pd.DataFrame, dest: Path) -> Path:
    out = frame.copy()
    for col in out.select_dtypes(include=["object", "string"]).columns:
        out[col] = out[col].astype("string").str.replace(r"[\t\r\n]+", " ", regex=True)  # tabs cannot be quoted
    out.to_csv(dest, sep="\t", index=False, quoting=3, lineterminator="\r\n", encoding="utf-8")
    return dest

FIELDS = [("customer_id", 10), ("name", 30), ("amount_cents", 12), ("booked", 8)]

def export_fixed_width(frame: pd.DataFrame, dest: Path) -> Path:
    lines = []
    for row in frame.itertuples(index=False):
        parts = []
        for (name, width) in FIELDS:
            value = str(getattr(row, name) or "")
            if len(value) > width:
                raise ValueError(f"{name}={value!r} exceeds {width} characters")   # never silently truncate
            parts.append(value.rjust(width, "0") if name.endswith(("_id", "_cents")) else value.ljust(width))
        lines.append("".join(parts))
    dest.write_text("\r\n".join(lines) + "\r\n", encoding="ascii", errors="strict")
    return dest

Fixed-width formats fail loudly here on both over-long values and non-ASCII characters, which is correct: a truncated account number or a silently replaced ü is worse than a failed export. Amounts in such formats are usually integer cents with implied decimals — convert with Decimal rather than floats to avoid rounding surprises.

Adding a new recipient First obtain a sample file the recipient accepts, and their written specification if one exists. Sniff the sample to read the delimiter, quote character, line terminator and encoding. Write the dialect into the recipients table next to the code. Export a test file containing awkward values such as embedded delimiters, quotes, accents and long numbers. Have the recipient import the test file and confirm, then store the confirmed dialect and the test file as a regression fixture. Get a sample a file they accept, plus any written spec Sniff it delimiter, quoting, line terminator, encoding Record the dialect one entry in RECIPIENTS, in version control Export awkward values delimiters, quotes, accents, long codes Confirm and freeze recipient imports it; keep the file as a fixture

Documenting the Dialect for the Recipient

Half of the friction in file exchanges is that neither side has written down what the file is supposed to look like. Generate a short specification from the same dialect object that produces the file, and ship it alongside the first delivery:

# pip install "pandas>=2.2"
import csv
from pathlib import Path

QUOTING_NAMES = {csv.QUOTE_MINIMAL: "only when needed", csv.QUOTE_ALL: "every field",
                 csv.QUOTE_NONNUMERIC: "non-numeric fields", csv.QUOTE_NONE: "never"}

def write_spec(recipient: str, dest: Path, columns: list[str]) -> Path:
    d = RECIPIENTS[recipient]
    lines = [
        f"File specification for {recipient}",
        "=" * 40,
        f"Delimiter        : {d.sep!r}",
        f"Quote character  : {d.quotechar!r} ({QUOTING_NAMES[d.quoting]})",
        f"Escaping         : {'doubled quotes' if d.doublequote else f'escape char {d.escapechar!r}'}",
        f"Line terminator  : {'CRLF' if d.lineterminator == chr(13) + chr(10) else 'LF'}",
        f"Encoding         : {d.encoding}",
        f"Decimal separator: {d.decimal!r}",
        f"Date format      : {d.date_format}",
        f"Missing values   : {'empty field' if d.na_rep == '' else d.na_rep!r}",
        f"Header row       : yes, columns in this order: {', '.join(columns)}",
    ]
    dest.write_text("\n".join(lines) + "\n", encoding="utf-8")
    return dest

Because the text is derived from the dialect that generates the file, the specification cannot drift from reality — a change to the dialect changes the document on the next run. Send it once, keep it in the repository, and refer to it when a recipient reports a parsing problem; most such reports turn out to be a change on their side.

Verification

Read every export back with the same dialect and compare against the source frame, then check the bytes for the properties a recipient's parser cares about.

# pip install "pandas>=2.2"
import csv
from pathlib import Path
import pandas as pd

def verify_export(frame: pd.DataFrame, dest: Path, recipient: str) -> None:
    d = RECIPIENTS[recipient]
    back = pd.read_csv(dest, sep=d.sep, quotechar=d.quotechar, escapechar=d.escapechar,
                       doublequote=d.doublequote, encoding=d.encoding, decimal=d.decimal,
                       dtype="string", keep_default_na=False)
    assert list(back.columns) == list(frame.columns), f"columns differ: {list(back.columns)}"
    assert len(back) == len(frame), f"{len(back)} rows read, {len(frame)} written"
    raw = dest.read_bytes()
    if d.lineterminator == "\r\n":
        assert raw.count(b"\r\n") == len(frame) + 1, "line terminators are not CRLF on every line"
        assert b"\n\n" not in raw.replace(b"\r\n", b"\n"), "blank lines present"
    if d.quoting == csv.QUOTE_NONE:
        assert d.sep.encode() not in raw.split(b"\r\n")[1].replace(
            d.sep.encode(), b"", len(frame.columns) - 1), "unquoted value contains the delimiter"
    field_counts = {line.count(d.sep.encode()) for line in raw.split(b"\r\n") if line}
    assert len(field_counts) == 1, f"inconsistent field counts per line: {sorted(field_counts)}"
    print(f"{dest.name}: {len(back)} rows, dialect '{recipient}' verified")

The field-count check is the one that catches the failure recipients actually report — unexpected number of fields on line 42 — because it finds the one row whose value contained an unescaped delimiter. Keep a fixture file per recipient containing deliberately awkward values and run the check on every release.

FAQ

Why does Excel open my comma CSV in one column? Excel uses the system list separator. In locales where that is a semicolon, a comma file lands in one column. Export with semicolons for those users, or add a sep=, line as the first line, which Excel honours and most other parsers do not.

Should I use QUOTE_ALL? It is the safest option for recipients whose parser is unknown, at the cost of a slightly larger file. Some strict importers reject quoted numeric fields, so confirm with a sample.

What about lineterminator versus line_terminator? Current pandas uses lineterminator; the old line_terminator spelling was removed in pandas 2.0.

Is a BOM required for Excel? Not required, but encoding="utf-8-sig" makes Excel recognise UTF-8 on double-click, which prevents accented characters turning into mojibake — see fixing encoding errors in CSV files.

Part of Exporting Data to CSV Formats.