Messy CSV to checked Excel: 1,000 records reconciled

Independent portfolio demonstration using online CC0 synthetic test data.

THE BEFORE ExploreMyData’s deliberately messy 1,000-record CSV contains duplicate headers, mixed date formats, mixed decimal conventions, damaged accents, padded fields, blank records and records with missing or extra fields. I kept the downloaded CSV unchanged.

THE WORK I normalized accepted values, preserved seven-character invoice IDs as text, converted dates and amounts to usable Excel values, and kept quoted text and embedded note line breaks intact. Duplicate notes headers became notes_1 and notes_2 by position. Records with uncertain structure went into a separate review queue rather than receiving invented fixes.

ACTUAL BEFORE AND AFTER Invoice 0000001: amount 2.637,12 became numeric 2637.12, displayed as 2,637.12 USD. Invoice 0000002: date 03/14/2026 became 2026-03-14. Invoice 0000005: damaged name Søren Kierkegaard was reversibly repaired to Søren Kierkegaard. Invoice 0000004: eight fields instead of seven. Quarantined with its original values; nothing guessed.

VERIFIED RESULT 1,000 source records = 809 accepted + 108 held for review + 83 blank records logged. 1,805 cell changes are documented individually: 809 amounts, 589 dates, 162 currency labels, 98 names and 147 notes. Every source record is accounted for.

THE HANDOFF An editable Excel workbook with overview, accepted data, exceptions and original source values. Also: cleaned CSV, complete exception file, cell-level before/after log, source checksum, repeatable cleaning script and eight passing focused tests. Workbook totals and a reversible input-change test were checked.

SCOPE AND SOURCE This parser stress test uses fictional names and a deliberately high error rate. The original format is CSV; Excel is part of the completed handoff. Native Microsoft Excel application behavior has not yet been tested. New files need their own agreed schema and rules.

Source and permission: https://exploremydata.com/datasets/messy-data-pack CC0 1.0: https://creativecommons.org/publicdomain/zero/1.0/

Need a similar cleanup? Send a redacted sample, the required output format, and the rules for uncertain records.