CSV & EDI Parsing Workflows

In vendor rebate and trade promotion reconciliation, every downstream accrual, deduction match, and settlement figure is only as trustworthy as the bytes that entered the pipeline. CSV and EDI parsing is the translation layer that turns a heterogeneous pile of SFTP drops, EDI VAN transmissions, and cloud-storage uploads into typed, hashable records the rest of the system can reason over. Within the broader data ingestion normalization pipelines discipline, this page owns one specific sub-problem: how a raw 810 invoice or a delimiter-drifting POS export becomes a deterministic source envelope and a set of normalized transaction facts, before any field is mapped or any rate is rated. Get the parser wrong and the failures are financial — a double-counted accrual from a re-sent file, a SAC allowance silently dropped because a loop boundary was misread, an encoding artefact that breaks a downstream join and surfaces weeks later as a deduction dispute.

This is the canonical reference for that parsing stage. It describes the envelope and fact schema the parser emits, how syntactic structure and control numbers are validated, how monetary and quantity values are extracted without precision loss, the Pydantic-enforced ETL patterns that make ingestion safe to replay, and the drift, dispute, and access controls that let trade finance defend every parsed number. The audience is concrete: Python ETL developers who own the parser, trade finance analysts who reconcile the resulting accrual, and vendor managers who answer for a contested claim. Once a payload clears this stage, it is handed to the field-mapping layer that aligns vendor labels to the canonical ledger schema.

Positioning Within the Reconciliation Architecture

The parsing layer sits at the very front of the reconciliation pipeline, upstream of everything financial. It reads files it did not produce, in formats it does not control, and emits a clean interface that the next stages depend on without re-reading the raw bytes. Vendor submissions arrive via SFTP, secure API endpoints, EDI value-added networks, or cloud-storage triggers, and the first action is never to parse — it is to quarantine, checksum, and register the payload against an ingestion manifest that records source system, vendor ID, transport, promotion period, receipt timestamp, and a byte-level content hash. Only then does parsing begin, and it begins in a worker pool rather than a monolithic thread so a single malformed transmission cannot stall an entire vendor batch.

Two design commitments keep this safe. First, nothing is mutated in place: the raw payload and its content hash are preserved as a forensic anchor, and every later transformation references the parser version that produced it. Second, the parser emits structure, not interpretation — it extracts segments and columns into typed facts but does not decide what a vendor’s PROMO_CD means; that semantic alignment belongs to field mapping strategies, which consumes the parser’s output. Clean separation here is what lets the pos & erp sync patterns layer and the downstream accrual engine treat parsed records as a stable contract rather than a moving target. Parsing throughput is also where peak-period scale is won or lost, which is why execution is delegated to the async batch processing layer rather than run inline at the point of receipt.

Entity Topology and Schema Specification

A parser is only deterministic if the records it emits are stable and hashable. Each inbound file resolves to one source envelope and many transaction facts, and the keys binding them must be assigned at parse time, never inferred later at query time. The envelope is the immutable record of what arrived; the fact is a single normalized line — a CSV row, an EDI IT1 line item, or an SAC allowance segment. The table below specifies the minimum schema the parser emits; treat it as a versioned interface, where adding a nullable field is backward-compatible while renaming or retyping one is a breaking change that bumps parser_version.

The record_key is what makes every later step idempotent: because it is a deterministic hash over the envelope id, the line ordinal, and the parser version, re-parsing an unchanged file yields identical keys and an upsert becomes a no-op rather than a duplicate accrual. Monetary and quantity values are held as Decimal from the moment they leave the parser — never float — so no rounding drift is introduced before the value is even mapped.

Conditional Logic and Rule Integration

Before any extraction runs, each file passes a deterministic structural gate that decides whether it is parseable and which lane it takes. Format detection is a discriminator, not a guess: a payload whose first bytes are an ISA segment (or whose envelope header is detected after BOM stripping) routes to the X12 lane; everything else is sniffed for a CSV dialect. The gate encodes three families of predicate.

CSV structural predicates. Production-grade reconciliation cannot assume well-formed CSV. The parser enforces strict RFC 4180 compliance via the standard-library csv module with an explicit dialect, defending against inconsistent quoting, delimiter drift, embedded newlines inside quoted fields, and mismatched line endings. Column-count verification rejects or quarantines any row whose arity deviates from the header; BOM markers and UTF-8/CP1252 mismatches are normalized before tokenization so a stray byte cannot shift every column one position to the right.

EDI X12 structural predicates. ASC X12 transaction sets — 810 (Invoice), 850 (Purchase Order), 852 (Product Activity) — are not flat. They are hierarchical envelopes (ISA/GS/ST opening, SE/GE/IEA closing) wrapping nested loops: N1 for parties, IT1 for line items, SAC for allowances and charges. A critical and frequently-missed detail: X12 segments are delimited by the terminator declared in ISA16 (commonly ~), not by newlines. Newlines may appear for human readability but are not part of the syntax, so the parser splits on the declared terminator after stripping cosmetic whitespace, never on line breaks. The parser maintains an explicit loop stack so a SAC segment is attributed to the correct IT1 line rather than a sibling, because for trade finance the SAC segment is the promotional allowance, off-invoice discount, or rebate accrual.

Temporal and scope predicates. The parser stamps each fact with a normalized business_date and the promotion period drawn from the ingestion manifest, but it does not adjudicate eligibility — it only carries the values forward so the downstream engine can window-match them. Encoding these as parse-time attributes rather than post-hoc lookups is what keeps the boundary between parsing and rating clean.

{
  "format_router": {
    "x12": {"detect": "first_segment == 'ISA'", "segment_terminator": "ISA16", "element_separator": "ISA04"},
    "csv": {"detect": "dialect_sniff", "strict_arity": true, "encoding_fallbacks": ["utf-8", "cp1252"]}
  },
  "quarantine_on": ["arity_mismatch", "control_number_mismatch", "unknown_encoding"]
}

Financial Settlement Layer

Parsing does not rate volume, but it is the first place where financial precision can be irreversibly lost, so the rules here are strict. Every monetary element — an IT1 extended amount, a SAC05 allowance amount, a CSV disc_amt column — is parsed directly into decimal.Decimal from its string form, with the implied-decimal conventions of X12 honoured explicitly. X12 numeric elements often carry an implied decimal point or an R-format real; reading these as float would inject platform-dependent drift that is invisible on one line but material across a quarter-end run, and because IFRS 15 / ASC 606 treatment requires figures reproducible to the cent, float is forbidden anywhere in the extraction path.

Quantities are preserved in their as-reported unit of measure with the original UoM code retained, so a later case-vs-each conversion is auditable rather than baked in. Currency is carried as the as-reported ISO 4217 code and never converted inside the parser — conversion is a downstream concern so the parsing path reasons in a single, faithful representation of the source. The example below shows the only acceptable extraction idiom: string in, Decimal out, with explicit scale.

from decimal import Decimal, ROUND_HALF_UP

def parse_x12_amount(raw: str, implied_decimals: int = 2) -> Decimal:
    """X12 monetary elements may carry an implied decimal; never route through float."""
    sign = Decimal("-1") if raw.startswith("-") else Decimal("1")
    digits = raw.lstrip("+-")
    if "." in digits:                       # explicit decimal present
        value = Decimal(digits)
    else:                                   # implied decimal per element spec
        value = Decimal(digits).scaleb(-implied_decimals)
    return (sign * value).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

ETL Implementation Patterns

Enforcement begins at the parser boundary. Model the parsed record with Pydantic v2 so a negative quantity, a float amount, or a missing control number fails fast at ingestion instead of surfacing as a bad accrual weeks later. The validator below rejects the two most common silent corruptions — a non-decimal amount and an envelope whose SE segment count disagrees with the segments actually read.

from decimal import Decimal
from pydantic import BaseModel, field_validator, model_validator

class TransactionFact(BaseModel):
    envelope_id: str
    line_ordinal: int
    segment_path: str | None = None
    raw_sku: str
    quantity: Decimal
    amount: Decimal
    currency: str

    @field_validator("quantity", "amount", mode="before")
    @classmethod
    def _must_be_decimal_str(cls, v: object) -> Decimal:
        if isinstance(v, float):
            raise ValueError("monetary/quantity values must arrive as str or Decimal, never float")
        return Decimal(str(v))

class SourceEnvelope(BaseModel):
    envelope_id: str
    content_hash: str
    parser_version: str
    declared_segment_count: int
    read_segment_count: int

    @model_validator(mode="after")
    def _control_total(self) -> "SourceEnvelope":
        if self.declared_segment_count != self.read_segment_count:
            raise ValueError("SE segment count mismatch — transaction set is truncated or corrupt")
        return self

Persistence uses idempotent upserts keyed on record_key, so a vendor re-sending yesterday’s file overwrites cleanly instead of duplicating accruals — the single most common source of phantom liabilities in hand-rolled pipelines. Duplicate whole-file submissions are caught even earlier, by comparing content_hash before a single segment is parsed. Schema evolution follows semantic versioning: a new optional column or segment handler ships behind a dual-read window and bumps parser_version, so historical envelopes remain reproducible under the exact parser that first read them. High-volume batches are processed out-of-order and statelessly, which is why the parser holds no cross-file state and defers orchestration to the async batch processing layer. Transaction-set-specific extraction — 810 loop iteration, SAC attribution, and control-number reconciliation — is worked end to end in parsing EDI 810 invoices with Python.

Drift Detection and Validation

A parser is only trustworthy if its output keeps matching the shape the downstream ledger expects. Drift detection runs statistical and control-total checks over each batch and quarantines anything that fails rather than discarding it — every quarantined record keeps its mismatch reason and an exception ticket is raised, so nothing is lost and nothing is silently accrued. Validation against the official ASC X12 element specs (ASC X12 transaction sets) catches segment-length and data-type violations before they reach mapping.

Catching these before the records reach the accrual engine is what preserves margin integrity: a control break caught at parse time is a vendor re-send request, while the same corruption discovered after settlement is a restated accrual and an audit finding. When drift exceeds a defined tolerance the parser triggers an automated exception workflow that routes the batch to operations with the offending record hash attached.

Fallback and Dispute Routing

Malformed transmissions are inevitable in a multi-vendor ecosystem, so the parser degrades rather than halts. Failures are categorized deterministically and routed by tier, never dropped. Format/encoding errors (truncated files, unsupported character sets, delimiter drift) are typically recoverable and trigger a vendor re-send notification. Schema/validation errors (missing mandatory segments, failed control-number checks, out-of-range elements) route to the vendor dispute workflow. Business-context errors (syntactically valid but referencing an unregistered vendor or a duplicate invoice number) are flagged for trade finance review. Each error payload carries a deterministic error code, the offending record hash, and a suggested remediation path; dead-letter queues retain failed payloads for auditability while automated alerting notifies vendor managers and the on-call ETL engineer. The escalation and adjudication mechanics of those queues — manual review, default-rate policy, and audit-log entries — are owned downstream, but the parser’s job is to name the correct lane and emit an audit entry recording why no clean parse was possible and where the record was routed, so reconciliation stays continuous and every non-standard payload is traceable.

Security and Access Boundaries

Vendor feeds carry sensitive negotiated pricing and party data, so parsing is governed as a financial control, not a utility script. Inbound payloads land in an access-restricted quarantine zone; raw envelopes are encrypted at rest, and field-level encryption protects monetary elements and party identifiers within parsed facts. Role-based access control (RBAC) tags travel with every entity so authorization is enforced per field: ETL developers can deploy parser versions but cannot mutate a persisted envelope, vendor managers can review quarantined submissions but cannot edit extracted amounts, and trade finance analysts can adjudicate exceptions and export audit trails but cannot alter source bytes. Immutable, hash-chained audit logs capture every parse, quarantine, and reprocess event — and because each envelope is anchored by its content_hash, any tampering surfaces as a chain break rather than passing unnoticed, aligning with SOX and internal-control requirements. SFTP keys and VAN credentials rotate on a fixed schedule. Treating CSV and EDI parsing as a versioned, access-governed control is what turns chaotic vendor submissions into audit-ready financial data: faster settlements, fewer disputes, and a defensible trail from raw byte to posted accrual.

Frequently Asked Questions

Why split X12 on the segment terminator instead of on newlines? Because newlines are cosmetic in X12. The real segment terminator is declared in ISA16 (commonly ~); a transmission may have no line breaks at all, or may contain newlines purely for readability. Splitting on \n will merge or fracture segments unpredictably, so the parser reads ISA16, strips cosmetic whitespace, and splits on the declared terminator.

How does the parser prevent a re-sent file from double-counting accruals? Two layers. A whole-file duplicate is caught before parsing by comparing the content_hash of the payload against prior envelopes. At the row level, every fact carries a deterministic record_key (a hash of envelope id, line ordinal, and parser version), and persistence is an idempotent upsert on that key, so replaying an unchanged file overwrites rather than inserts.

Why parse monetary values as decimal.Decimal and never float? X12 amounts often use an implied decimal point, and float introduces platform-dependent rounding drift that is invisible per line but material across a quarter-end run. IFRS 15 / ASC 606 treatment requires figures reproducible to the cent, so amounts are parsed from their string form straight into Decimal with an explicit scale and ROUND_HALF_UP.

What happens to a row that fails validation — is it dropped? Never dropped. Failed records are quarantined to a holding table with their mismatch reason and an exception ticket, and routed by error tier: recoverable format errors trigger a vendor re-send, schema failures go to the dispute workflow, and business-context errors are flagged for trade finance. Dead-letter retention preserves the payload for audit.

Related

• Field Mapping Strategies — aligns the parser’s raw vendor labels to the canonical ledger schema.
• POS & ERP Sync Patterns — how parsed, mapped payloads are batched and pushed to downstream financial systems.
• Async Batch Processing — the queue and worker-pool execution model that scales parsing through peak periods.
• Parsing EDI 810 Invoices with Python — segment iteration, SAC attribution, and control-number reconciliation for the 810.

Up one level: Data Ingestion & Normalization Pipelines