CSV cleanup without silent data loss: a 16-row worked example

A CSV can look tidier after cleanup and still be less trustworthy. Turning an identifier into a number can erase leading zeros. Picking the newest of two conflicting records can discard a fact the owner needed to review.

This worked example uses original synthetic data, not customer records or a testimonial. The approach is conservative: normalize only what an explicit rule permits, preserve the source, and report every unresolved decision. This article and demonstration were created with AI assistance.

Start with a policy, not a delete button

The example contains 16 records with six columns: SKU, product name, USD unit price, stock count, updated date, and category. Before changing anything, its rules distinguish display text from business identifiers.

• Product names and categories may have repeated or surrounding whitespace normalized.
• SKU values remain text and are preserved exactly. The identifiers 0001 and 1 are distinct. Surrounding spaces in a SKU are flagged instead of silently trimmed.
• Prices must match the example's documented USD format. Values with an ambiguous decimal convention or more than two decimal places go to review, without rounding.
• Stock may be normalized only when it is a nonnegative integer.
• Dates must already be valid, unambiguous ISO calendar dates.

These are example rules, not universal business rules. Negative stock might represent backorders in another system. A real cleanup must agree that policy with the data owner.

Separate normalization from interpretation

Here are actual decisions from the demonstration:

A price that can be interpreted is not necessarily a price that should be automatically changed.

Exact duplicate rows and duplicate identifiers are different

Two source records for SKU 0003 are identical across the demonstrated fields. The example removes one and records which source row it matched.

The two records for 0010 have different prices and dates. Both remain. The date alone does not establish whether one supersedes the other, or whether an upstream mapping problem created the conflict.

This distinction is why “remove duplicates” needs a precise key and a conflict policy.

Make the result auditable

The run produces 15 retained records, one documented exact duplicate removal, and eight field changes. Eleven retained records still need review. That is a useful output: uncertainty is visible rather than disguised as a finished import.

The deliverables separate the questions a reviewer needs to answer:

• cleaned.csv contains retained records, source-row references, and review flags.
• changes.csv lists the field, original value, replacement, and rule for each change.
• review.csv lists unresolved issues and their reasons.
• removed.csv accounts for the exact duplicate and its matching source row.
• report.md explains the policy, counts, and remaining limitations.

Reconcile source records before trusting the result: 16 source records = 15 retained + 1 removed. Count field changes separately from changed rows. One record can contain more than one field change.

The source file remains untouched. When importing the output into a spreadsheet, explicitly import identifiers as text: CSV itself cannot force the spreadsheet to preserve their type. This example is not certified import-ready while unresolved records remain.

Inspect the files or scope a cleanup

The free before-and-after sample includes the source, output, change log, review list, removed-row record, and report so you can inspect these decisions yourself.

VisitMargin also offers a separately scoped $49 cleanup for one CSV up to 5,000 rows and 15 columns. The sample page explains the offer and contact route. Scope, permitted changes, and timing are agreed before payment; please start with a redacted sample rather than sensitive records.