Comparing two CSV exports is rarely a simple row-by-row diff. This repeatable workflow shows how to profile files, map different columns, normalize values, generate likely matches, classify changes, review uncertain pairs, and produce an audit-friendly reconciliation report.
Overview
A CSV comparison becomes an entity resolution problem when the same real-world record appears differently in each file. A customer may be written as “M. Garcia” in one export and “Maria Garcia” in another. An address may contain a different abbreviation, a company may have gained a legal suffix, or one system may use an internal identifier that the other does not contain.
The goal is not to find the most similar string for every row. The goal is to decide whether two rows refer to the same entity, then explain that decision. A useful reconciliation process should distinguish at least five outcomes:
- Matched and unchanged: the records refer to the same entity and relevant values are equivalent.
- Matched with changes: the records refer to the same entity, but one or more fields differ.
- Only in the first file: no sufficiently supported counterpart was found.
- Only in the second file: the row appears newly added or was absent from the earlier export.
- Ambiguous or duplicate: more than one plausible counterpart exists, so a person or stricter rule must review it.
Keep the original values, normalized values, match evidence, score, decision, and review status in the output. This makes the result reproducible and prevents a fuzzy match from becoming an unexplained merge. If the files contain customer or other sensitive data, restrict access, avoid unnecessary copies, and remove raw values from logs and screenshots.
Checklist by scenario
1. Before comparing any files
- Record the file names, export dates, source systems, row counts, and expected grain. Confirm whether one row represents a person, account, address, order, product, or another entity.
- Check the delimiter, character encoding, quote behavior, header row, line endings, and whether empty strings represent missing values. Do not assume every “CSV” uses commas or UTF-8.
- Profile each column: data type, null rate, distinct count, common formats, and examples. Look for repeated headers, footer rows, totals, and malformed records.
- Identify an authoritative identifier if one exists. An exact stable ID should usually take precedence over a fuzzy name or address match, but validate that the ID is unique and not reused.
2. When column names differ
Build an explicit mapping rather than relying on column position. Document whether a field is a direct equivalent, a partial equivalent, derived, or unavailable.
| File A | File B | Relationship | Matching use | Notes |
|---|---|---|---|---|
| customer_id | account_number | Possible identifier | Primary key if validated | Check uniqueness and reuse |
| full_name | first_name + last_name | Composite equivalent | Supporting field | Handle order and missing parts |
| phone | mobile | Likely equivalent | Strong supporting field | Normalize country and extension rules |
| address | street + city + postal_code | Expanded or reconstructed | Candidate generation and review | Do not compare only raw strings |
| status | lifecycle_stage | Business mapping required | Change detection only | Define equivalent states first |
3. When formatting differs
Create a documented normalization pipeline for each field. Typical steps include trimming whitespace, converting repeated spaces to one space, applying consistent case folding, Unicode normalization, standardizing punctuation, and converting known null markers such as “N/A” or “unknown” to a single missing value. For phone numbers, use a defined country assumption and separate extensions. For addresses, standardize abbreviations only when the rule is appropriate to the geography.
Keep normalized values in separate columns. Never overwrite the source values. Normalization should reduce irrelevant variation, not erase distinctions: “12” and “120” are not equivalent, and removing apartment numbers can create false matches.
4. When no reliable identifier exists
Use a two-stage process: candidate generation followed by scoring. Candidate generation narrows the search with blocking keys such as postal code, email domain, phone suffix, country, or the first characters of a normalized name. Then compare candidates using several fields instead of applying one global string threshold.
For example, a candidate could receive stronger evidence from an exact normalized email, moderate evidence from a close name, and weaker evidence from a partial address. Similarity measures such as Levenshtein distance, Jaro-Winkler, or trigram similarity can help, but their scores are not interchangeable. Test thresholds on reviewed examples; guidance on threshold testing and algorithm selection is available in this fuzzy matching algorithm comparison.
5. When the output will drive updates or merges
Separate detection from action. First create a reconciliation report. Only after review should an approved process update records or merge duplicates. For customer data, define survivorship rules, preserve an audit trail, and provide a rollback path. See the safe deduplication workflow for a fuller treatment.
What to double-check
Match decisions and confidence bands
Use decision bands rather than presenting every score as equally trustworthy. A practical template is:
| Decision | Evidence pattern | Action |
|---|---|---|
| Confirmed | Validated stable ID, or multiple independent fields agree with no contradiction | Accept automatically if business risk permits |
| Likely | Strong combined similarity but no authoritative identifier | Sample, review, or route through controlled approval |
| Ambiguous | Top candidates are close, or important fields conflict | Require manual review |
| Unmatched | No candidate passes the minimum evidence rule | Keep as new, missing, or unresolved |
A high score on a common name is weaker than a moderate score supported by a unique phone number and matching postal code. Also inspect the margin between the best and second-best candidate. A top score that barely beats another candidate deserves review.
Change detection after matching
Compare fields only after the entity pair is established. Classify differences as meaningful, formatting-only, expected transformation, or conflict. For example, a case difference in a name may be harmless, while a changed bank account, tax identifier, or consent status may require a separate control. Define field-specific comparison rules instead of treating every unequal string as a business change.
Duplicates within each file
Before cross-file matching, check for duplicate identifiers and repeated normalized combinations within each file. A single row in File A matching three rows in File B may indicate legitimate one-to-many relationships, duplicated exports, or an unsafe matching rule. Record the cardinality of every accepted relationship: one-to-one, one-to-many, many-to-one, or many-to-many.
For multilingual names or transliterated text, test language-aware normalization and candidate generation. Phonetic methods can help with some name variations but can also collapse unrelated names; review their failure modes before using them as a decisive key. The guides on phonetic name matching and multilingual entity matching provide useful safeguards.
Reconciliation report fields
Include at least: source row identifiers, matched row identifiers, decision, score or evidence summary, candidate count, changed fields, duplicate flags, reviewer, review date, and reason code. A reason code such as exact_id, multi_field_match, conflicting_phone, or no_candidate is easier to audit than a free-text note alone.
Common mistakes
- Comparing rows by position. Sorting, filtering, and inserted records make row numbers unreliable. Match entities first.
- Using one fuzzy threshold for every field. Names, emails, postal codes, product codes, and addresses have different error patterns and risks.
- Normalizing too aggressively. Removing all punctuation, digits, or apartment details may turn distinct entities into the same key.
- Accepting the nearest candidate automatically. The nearest row may still be wrong, especially when a field is common or missing.
- Ignoring null semantics. Two blank fields do not necessarily provide positive matching evidence. Treat missingness separately from equality.
- Mixing detection with mutation. Do not overwrite a production table while experimenting with thresholds. Export proposed actions and approve them separately.
- Failing to preserve provenance. Without source file versions, row references, and normalization rules, the result cannot be reproduced when someone questions it.
- Logging sensitive raw data. Use masked identifiers in diagnostics and limit access to working files and reports.
When to revisit
Run this checklist before every recurring reconciliation, especially after a seasonal planning cycle, CRM migration, vendor change, schema revision, or export-tool update. Revisit the workflow sooner when unmatched rates rise, duplicate patterns change, a source adds or removes columns, or users report false matches.
Compare each new report with the previous run. Track row counts, match rates by decision band, unmatched records by source, one-to-many relationships, changed fields, and the number of manual overrides. A trend is often more informative than a single run: a sudden increase in “likely” matches may indicate formatting drift, while a drop in confirmed matches may indicate an identifier change.
Maintain a small reviewed sample of confirmed, rejected, and ambiguous pairs. Re-run it whenever normalization rules, libraries, database extensions, or matching thresholds change. This regression set helps reveal whether an improvement in recall has created unacceptable false positives. For larger programs, turn reviewed decisions into a labeled evaluation set as described in the gold-standard entity resolution guide.
Action checklist for the next run: version both input files; validate parsing; profile columns; update the mapping table; run field-specific normalization; validate stable identifiers; generate blocked candidates; score and band decisions; inspect ambiguous and one-to-many matches; export a report with evidence; approve changes separately; and archive the rules and review notes with the result.