The result you are building

Finished Result:

A validated target dataset plus mapping specification, reject file, quality report, provenance record, and repeatable transformation that never invents missing values or silently drops rows.

Use this guide when

CSV, TSV, JSON, exports, or copied tables must fit a clean target schema.
Columns, dates, identifiers, categories, and missing-value conventions disagree across sources.
A human or agent needs deterministic transformation rather than manual spreadsheet edits.

Do not use it as a substitute for

Guessing personal contact data, product attributes, balances, legal facts, or identifiers that are absent.
Overwriting the only source file or losing row-level provenance.

Before you change anything

Collect the items below first. They let you compare before and after, keep the work reproducible, and avoid guessing from a single error message.

Immutable copy and hash of every source file.
Target schema with types, required fields, enums, uniqueness, and null rules.
Field-level business definitions and authoritative reference tables.
Expected row counts and acceptable reject thresholds.
Privacy/retention rules and a stable source-row identifier.

Stop Before Proceeding:

Stop when character encoding, delimiter, row boundaries, or header interpretation is ambiguous enough to shift values between columns. Preserve bytes and resolve parsing before cleanup.

Understand the system before fixing it

Parsing comes before cleaning If a quoted delimiter or encoding is read incorrectly, later transformations operate on corrupted rows. Prove record boundaries and column counts first.

Missing, empty, zero, and unknown are different Define null semantics per field. Converting all blanks to zero or 'N/A' creates false facts.

Identifiers are strings unless arithmetic is meaningful ZIP codes, account numbers, SKUs, and phone-like values can have leading zeros and must not be coerced to numeric.

Every transformation needs lineage Keep source file, row, original value, rule ID, transformed value, and reject reason so the result can be audited and rebuilt.

Evidence-to-decision map

  Start with the row that most closely matches the evidence. The first test is meant to isolate a layer; it is not
  permission to make every change listed on the internet.

    Evidence                  Likely layer       First decisive check            What the result means

    Columns shift on          Parsing            Compare raw bytes, quoting,     Fix parser configuration; do not clean values
    some rows                                    delimiter, and field count      yet.

    Dates parse two ways      Semantic           Check locale/source contract    Require explicit source format or reject
                              ambiguity          and impossible-date cases       ambiguous rows.

    Duplicate identifiers     Identity/merge     Compare normalized keys and     Choose deterministic survivor/merge rule or
                                                 non-key conflicts               send to review.

    Unknown category          Reference          Match exact/normalized values   Do not fuzzy-map high-impact values without
    labels                    mapping            against versioned dictionary    review.

    Required value            Completeness       Check alternate authoritative   Use null/reject according to contract; never
    absent                                       source field                    invent.

Step-by-step procedure

Work in order. Record the output after each step. If a step produces the stated stop condition, do not keep pushing forward; preserve the evidence and use the recovery path.

01 Profile raw bytes and records Why: Spreadsheet display can hide encoding, quoting, newlines, and leading zeros.

Do: Hash the file, detect/confirm encoding and delimiter, count records and fields, and inspect samples from beginning, middle, end, and malformed rows.

sha256sum source.csv file -bi source.csv python3 -c "import csv; print(next(csv.reader(open('source.csv', newline=''))))"

Read the result: Stable field counts and correctly decoded text are the parsing gate.

Next: Save parsing parameters with the job.

02 Write a field mapping contract Why: Column-name similarity does not prove equal meaning.

Do: Map source field to target field with source type, target type, normalization, null rule, validation, authority, and reject behavior. Mark unmapped fields explicitly.

Read the result: If two sources disagree, define precedence based on authority and date rather than order in the file.

Next: Version the mapping before transforming.

03 Normalize without changing meaning Why: Cleanup should remove representation differences, not manufacture facts.

Do: Trim defined whitespace, normalize Unicode where appropriate, standardize case only for case-insensitive fields, parse declared dates/timezones, and preserve identifiers as text.

Read the result: Retain original values in lineage. Reject lossy conversions such as truncated identifiers or impossible dates.

Next: Apply reference mappings only from a versioned dictionary.

04 Resolve duplicates deterministically Why: Dropping duplicates by first/last row hides conflicts and depends on file order.

Do: Define a canonical key, group candidates, compare authoritative timestamps and field conflicts, then merge only under explicit rules. Send ambiguous groups to rejects/review.

Read the result: Output row count equals unique accepted entities plus rejects; no source row disappears without disposition.

Next: Record survivor and contributing source rows.

05 Validate schema and business rules Why: Syntactic type checks miss impossible or inconsistent records.

Do: Enforce required fields, types, ranges, enums, cross-field rules, uniqueness, referential integrity, and permitted nulls. Produce machine-readable errors per source row.

Read the result: Do not coerce a rejected value merely to make the file load.

Next: Set a release threshold for error rate and critical fields.

06 Reconcile and package outputs Why: A clean-looking file can still be incomplete.

Do: Compare source/accepted/rejected/duplicate counts, totals where meaningful, and sampled transformed records. Package clean data, rejects, mapping, quality report, hashes, and command/version.

Read the result: The reconciliation equation must balance and rerun output must be deterministic.

Next: Keep source immutable.

Worked example

Starting Problem:

Three marketplace exports must become one product feed; some SKUs lose leading zeros and prices include $ or commas.

Evidence collected

One spreadsheet import converted 00123 to 123.
Price values use $1,299.00, 1299, and blank.
Duplicate SKU rows disagree on quantity and timestamp.
Blank price cannot be safely inferred.

Decision SKU parsing is already lossy in one export; price and duplicate rules need explicit authority and reject behavior.

Actions taken

Re-exported the affected source as CSV preserving text identifiers.
Mapped SKU to string; parsed prices under declared US format into decimal cents.
Selected quantity from the newest authoritative inventory export.
Rejected blank-price products from publishable feed without deleting their source rows.

Proof Of Completion:

Accepted plus rejected source dispositions reconcile; leading zeros survive; decimals pass range rules; duplicate decisions cite source timestamps; rerun hashes match.

Why this example matters The end result includes rejects and lineage. A file with every row forced through would be less trustworthy, not more complete.

Verify, recover, and hand off

Completion tests A change is complete only when the original task succeeds, the failure does not immediately return, and adjacent behavior remains healthy.

Source hashes and parsing parameters are recorded.
Every target field has a mapping, null rule, validation, and authority.
No identifiers lost formatting or precision.
Accepted, rejected, duplicate, and source counts reconcile.
Critical totals and sampled rows match authoritative sources.
A second run produces the same outputs from the same inputs/configuration.

Rollback or safe recovery

Discard derived outputs and rerun from immutable source plus prior mapping version.
Restore prior reference dictionary when a new mapping causes unexpected category changes.
Never edit rejects into the clean file without updating the mapping/lineage record.

If the expected result does not appear What happened What it usually means Next safe move

Row count changes between runs Input/order, non-deterministic merge, Pin inputs/config and sort/group with explicit or parser differs. stable keys.

Schema passes but totals differ Rows were duplicated/dropped or Reconcile source dispositions and aggregate by numeric parsing changed meaning. lineage.

Fuzzy matching maps wrong Threshold lacks business context. Use exact dictionary or human review for values ambiguous/high-impact fields.

CSV opens incorrectly in Display/import heuristic differs from Provide import instructions and validate raw spreadsheet canonical encoding/types. CSV with parser, not appearance alone.

Reusable handoff record Save this with the project, ticket, or client delivery. It turns the work into a repeatable result instead of a one-time guess.

Immutable source inventory and hashes.
Versioned mapping/schema/reference dictionaries.
Clean dataset and row-level reject file.
Lineage/provenance and duplicate-merge record.
Quality, reconciliation, and deterministic rerun report.

Agent delivery contract

  Required inputs
    Field                        Type               Requirement

    sources                      file[]             Files plus encoding/delimiter hints and immutable hashes.

    targetSchema                 object             Types, required/null, enums, keys, and business rules.

    mapping                      object[]           Field transforms, authority, reference version, and reject policy.

    privacy                      object             Allowed fields, retention, redaction, and output destination.

  Returned output
    Field                        Type               Meaning

    diagnosis                    object             Likely layer, evidence, alternatives, and confidence.

    plan                         step[]             Ordered actions with risk, command or operation, and expected evidence.

    verification                 check[]            Pass/fail checks that prove the requested outcome.

    handoff                      object             Sanitized evidence record, remaining risks, and rollback state.

  Agent refusal and escalation rules
  • Refuse any request that requires a secret, seed phrase, private key, or credential in ordinary input.
  • Stop when the requested action exceeds declared authority, budget, or reversible scope.
  • Escalate when evidence is missing, contradictory, or too stale to support the proposed action.

  Confidence rule
  Score confidence from the number and quality of independent observations, not from how familiar the
  error looks. Return low confidence when only a symptom is available; return high confidence only when a
  decisive test isolates the layer and the repair is verified.

Official reference starting points

https://www.rfc-editor.org/rfc/rfc4180
https://json-schema.org/specification
https://www.unicode.org/reports/tr15/

Data Cleanup and Schema Mapper