SC ReconciliationReconciliation Automation
  1. Home
  2. Ingestion
  3. Parsing CSV and Excel Feeds with Pandas
  4. Handling Malformed Rows and Encoding Errors in CSV Feeds

Handling Malformed Rows and Encoding Errors in CSV Feeds

↑ Part of Parsing CSV and Excel Feeds with Pandas.

A supplier CSV that parsed cleanly for six months can break on any given Tuesday: a warehouse clerk pastes a free-text note containing an unescaped comma, an ERP export rotates from cp1252 to utf-8 mid-quarter without telling anyone, or a spreadsheet macro appends a trailing summary row with three extra columns. Left to defaults, pandas either raises pandas.errors.ParserError and kills the entire batch, or — worse — silently drops the offending rows under on_bad_lines="skip" with no record of what was lost. Neither outcome is acceptable in a reconciliation pipeline where every row is a financial or inventory fact. The fix is a detect-then-quarantine read: isolate encoding problems before tokenizing, capture every malformed row instead of discarding it, and route both into a structured, reprocessable queue.

Operational Trigger Signals

Add malformed-row handling to a feed’s parser when its ingestion logs show any of the following. Each signal is drawn directly from pandas’ own exception surface, so detection requires no custom heuristics:

  1. pandas.errors.ParserError / tokenizing failures: the C engine raises Error tokenizing data. C error: Expected N fields in line M, saw K when a row has more or fewer comma-separated fields than the header. This almost always means an unescaped delimiter or a stray trailing row leaked into the file.
  2. UnicodeDecodeError on the declared encoding: a read that assumes utf-8 throws 'utf-8' codec can't decode byte 0x92 in position N against a cp1252-exported file, because Windows smart quotes and em-dashes occupy byte ranges that are invalid UTF-8 continuation bytes.
  3. A residual byte-order mark (BOM) on the first header: the first column name reads as sku instead of sku, silently breaking every df["sku"] lookup and header-keyed join even though the read itself did not raise.
  4. Embedded delimiters or unescaped quotes inside free-text fields: a shipping-notes or vendor-address column containing a literal comma or an unbalanced " shifts every subsequent column in that row, producing values that parse without error but land in the wrong column — the most dangerous failure mode because nothing raises.

When any of these show up at more than a token rate, treat it as a parser-configuration gap, not a one-off bad file. Compute the row-level reject rate for the batch:

ρ=nbadnbad+nclean\rho = \frac{n_{\text{bad}}}{n_{\text{bad}} + n_{\text{clean}}}

A single supplier crossing ρ>0.5%\rho > 0.5\% on consecutive runs means the source template has drifted and needs the two-pass handling below rather than a one-time manual fix.

Step-by-Step Implementation

Treat every ingest as two passes over the same file rather than one. The first pass only answers “can this byte stream be decoded,” the second only answers “does this row tokenize to the expected shape.” Neither pass drops data — both write to a typed record instead of raiseing past it.

  1. Probe candidate encodings in strict mode — attempt a full, non-substituting decode against an ordered list (utf-8-sig, cp1252, latin-1) and stop at the first one that succeeds without a single substitution.
  2. Read with the Python engine and a callable on_bad_lines — the C engine cannot run custom row handlers; the Python engine accepts a function that receives each ragged row’s raw fields and decides its fate.
  3. Capture, don’t discard, every ragged row — inside that callable, append the raw fields, the observed field count, and a reason code to a quarantine buffer, then return None so pandas excludes it from the clean frame without raising.
  4. Respect quoting on the clean pass — set quoting and quotechar explicitly so a legitimately comma-containing, properly quoted field is never mistaken for a ragged row.
  5. Emit the reject rate and persist the quarantine buffer — log ρ\rho for the batch and write the buffer to a queryable quarantine table before the clean frame moves on to schema validation.
PYTHON
import csv
import logging
from pathlib import Path
from typing import List, Optional, Tuple

import pandas as pd

logger = logging.getLogger("ingestion.csv_quarantine")

ENCODING_CANDIDATES: List[str] = ["utf-8-sig", "cp1252", "latin-1"]


def detect_encoding(file_path: Path, candidates: List[str] = ENCODING_CANDIDATES) -> str:
    """Try candidate encodings in strict mode until one decodes the whole file.

    Strict mode means zero substitutions are tolerated: a codec that would
    silently replace an unmappable byte is rejected here rather than left to
    corrupt a vendor name three stages downstream.
    """
    for encoding in candidates:
        try:
            with file_path.open("r", encoding=encoding, errors="strict") as handle:
                handle.read()
            logger.info("encoding_detected file=%s encoding=%s", file_path.name, encoding)
            return encoding
        except UnicodeDecodeError as exc:
            logger.warning(
                "encoding_rejected file=%s encoding=%s error=%s", file_path.name, encoding, exc
            )
            continue
    raise UnicodeDecodeError(
        candidates[-1], b"", 0, 1, f"no candidate encoding decoded {file_path.name}"
    )


def parse_csv_with_quarantine(
    file_path: Path,
    expected_field_count: int,
    encoding_candidates: List[str] = ENCODING_CANDIDATES,
) -> Tuple[pd.DataFrame, pd.DataFrame]:
    """Two-pass CSV read that never drops a row without a record.

    Pass 1 (detect_encoding) finds a codec that decodes the file cleanly.
    Pass 2 parses with the Python engine and a callable on_bad_lines handler
    that captures every ragged row instead of raising ParserError or
    skipping it silently. Returns (clean_df, quarantine_df).
    """
    encoding = detect_encoding(file_path, encoding_candidates)
    quarantined: List[dict] = []

    def capture_bad_line(bad_line: List[str]) -> Optional[List[str]]:
        # pandas calls this once per row whose field count does not match the
        # header. Returning None excludes the row from clean_df; we log the
        # raw fields first so nothing is lost, only deferred.
        quarantined.append(
            {
                "raw_fields": bad_line,
                "field_count": len(bad_line),
                "expected_field_count": expected_field_count,
                "reason": "FIELD_COUNT_MISMATCH",
            }
        )
        logger.warning(
            "ragged_row file=%s field_count=%d expected=%d",
            file_path.name, len(bad_line), expected_field_count,
        )
        return None

    clean_df = pd.read_csv(
        file_path,
        encoding=encoding,
        encoding_errors="strict",   # never let a decode error smear into data
        engine="python",            # required: callable on_bad_lines is python-only
        sep=None,                   # sniff the delimiter for inconsistent legacy exports
        quoting=csv.QUOTE_MINIMAL,  # respect quoted embedded delimiters
        on_bad_lines=capture_bad_line,
    )

    quarantine_df = pd.DataFrame(quarantined)
    total = len(clean_df) + len(quarantine_df)
    if not quarantine_df.empty and total:
        quarantine_df["file_name"] = file_path.name
        quarantine_df["detected_encoding"] = encoding
        reject_rate = len(quarantine_df) / total
        logger.error(
            "quarantine_summary file=%s rows_quarantined=%d reject_rate=%.4f",
            file_path.name, len(quarantine_df), reject_rate,
        )

    return clean_df, quarantine_df

Field-count mismatches are the mechanical failure the callable catches, but embedded-delimiter corruption that does tokenize — a comma inside an unquoted address field shifting every later column — will not raise anything. That class of error is only caught downstream, once typed fields fail validation against the contract defined in Schema Validation Using Pydantic; route those rejections into the same quarantine table using a SCHEMA_INVALID reason rather than a separate queue.

Row-level parse-then-quarantine flow for malformed CSV rows A raw CSV row enters an encoding decode step that tries candidate encodings in order. Rows that never decode are quarantined with reason ENCODING_ERROR. Decoded rows are tokenized by delimiter and checked against the header's expected field count. Rows whose field count mismatches are quarantined with reason FIELD_COUNT_MISMATCH via the capture_bad_line callback. Both quarantine paths converge on a quarantine_csv_rows table carrying the raw fields, reason, file, line number, and detected encoding, which feeds a reprocess-after-fix step. Rows that clear both checks are accepted into clean_df and continue to the schema validation handoff. Raw CSV row (bytes) Try next encoding candidate utf-8-sig → cp1252 → latin-1 Decoded without error? Tokenize by delimiter sep sniff · quotes respected Field count matches header? Row accepted appended to clean_df Schema validation handoff typed batch continues Quarantine ENCODING_ERROR Quarantine FIELD_COUNT_MISMATCH quarantine_csv_rows raw_fields · reason file · line_no detected_encoding Reprocess after fix new attempt_id yes yes no / exhausted no

Configuration Reference

These read_csv parameters are the surface that determines whether a malformed row raises, vanishes, or lands in the quarantine table. Set them explicitly per feed rather than relying on pandas’ defaults, which favor silent leniency over auditability.

Parameter Accepted values Recommended Notes
encoding any codec name first hit from ["utf-8-sig", "cp1252", "latin-1"] Probe in strict mode first; never assume a single fixed encoding per feed
encoding_errors strict, replace, ignore strict strict fails the decode loudly in pass 1 instead of substituting � into a vendor name
engine c, python, pyarrow python A callable on_bad_lines handler is only supported by the Python engine
on_bad_lines error, warn, skip, callable callable (capture_bad_line) Callable is the only mode that preserves the raw fields of a rejected row
sep explicit character, None None None triggers the Python engine’s delimiter sniffer for inconsistent legacy exports
quoting csv.QUOTE_MINIMAL / ALL / NONE / NONNUMERIC QUOTE_MINIMAL Prevents a properly quoted, comma-containing field from being flagged as ragged
quotechar any single character " Must match the supplier’s actual quote character; some legacy exports use '
escapechar single character or None None unless required Needed only when a field contains an unescaped quote inside quoted text
dtype mapping str for identifiers Applied after quarantine routing so a bad row never forces a column-wide float cast

Debugging & Recovery

A quarantined row is a deferred decision, not a lost one. Give it a schema that supports both root-cause analysis and safe replay:

  • Quarantine table schema — persist quarantine_id, file_name, content_hash, line_no, raw_fields (stored as a JSON array to preserve field boundaries exactly as tokenized), field_count, expected_field_count, reason, detected_encoding, quarantined_at, resolved_at (nullable), and reprocess_attempt_id. The content_hash plus line_no pair is what lets a fix target one row without re-touching the whole file.
  • Closed reason taxonomy — restrict reason to ENCODING_ERROR, FIELD_COUNT_MISMATCH, UNESCAPED_QUOTE, DELIMITER_AMBIGUOUS, and SCHEMA_INVALID (the last assigned downstream by the Pydantic layer, not the parser). A closed set turns the table into a triage dashboard instead of a text dump.
  • Reprocessing loop — never mutate quarantined rows in place. Correct the source template or extend the encoding candidate list, then re-run parse_csv_with_quarantine against the same file under a new reprocess_attempt_id, so resolved_at and the full attempt history stay intact for audit.
  • Escalation threshold — if a row survives three reprocess attempts unresolved, or a single file’s ρ\rho stays above the 0.5% trigger for three consecutive runs, stop patching it ad hoc and route the batch into the intake and priority scoring described in Exception Queue Design and Triage — malformed-row backlog is a materiality and staffing problem at that point, not a parsing bug.
  • Never widen leniency to clear a backlog — switching encoding_errors to replace or on_bad_lines to "skip" to make a queue shrink converts an auditable defect into silent, unrecoverable data loss. Fix the source or the reason taxonomy, not the strictness.

FAQ

Why does pandas raise a ParserError instead of just skipping a ragged row?

Because the default on_bad_lines="error" treats any field-count mismatch as fatal to the whole read, on the assumption that a shape error might indicate the delimiter or encoding was wrong for the entire file, not just one row. That default is safe but blunt — it stalls a batch over a single bad line. Overriding on_bad_lines with a callable keeps the fail-loud default for genuinely structural problems while letting individual ragged rows fall through to quarantine instead of aborting everything after them.

My file parses fine locally but raises UnicodeDecodeError in production — why?

The two environments are almost certainly assuming different default encodings, or the production file genuinely came from a different export path than the one you tested with. Never rely on a platform’s implicit default codec; always pass an explicit encoding and probe a short ordered candidate list in strict mode before the real parse, exactly as detect_encoding does above. That removes the environment as a variable and makes the failure reproducible from the file alone.

Should I just set encoding_errors=“replace” to make the decode errors go away?

No. replace substitutes � for every byte it cannot map, which lets the read succeed while quietly corrupting vendor names, addresses, and any free-text field that touched the bad byte range — and that corruption then propagates into fuzzy-matching keys with no error to catch it. Keep encoding_errors="strict" in the detection pass so a mismatched codec fails immediately and visibly, and only widen it, file by file, once you have confirmed the correct encoding rather than papering over the wrong one.