Clean a CSV without losing information: a repeatable workflow

A clean-looking spreadsheet can still contain damaged identifiers, ambiguous dates, duplicated records, and silent omissions. This workflow is for analysts, researchers, and anyone receiving a CSV from another team. Its goal is a usable dataset with an explanation of every change.

1. Keep the original and define one row

Save the received file unchanged. Work on a copy and record its source, retrieval date, and intended use. If the file contains personal information, keep both versions in an appropriate private location.

Write one sentence defining a row: “One row is one order line,” for example. An order can have several lines, so a repeated order ID is not automatically a duplicate. Decide whether the expected key is order_id, a combination such as order_id + line_id, or something else.

2. Import deliberately

Use an import dialog that lets you choose column types before opening the file. Treat identifiers as text, even when they contain only digits. Otherwise 00127 can become 127, and long identifiers can lose precision. Changing the display format later cannot reliably recover lost information.

Confirm the delimiter and encoding. A comma inside a quoted field belongs to the field; splitting a line on every comma is not a CSV parser. W3C's tabular-data guidance recommends UTF-8 and describes quoting for embedded commas, quotes, and line breaks.

3. Make a small data dictionary

Before replacing values, agree on their meaning. Here is a fictional order-line schema:

Column	Meaning and storage	Validation rule
order_id	Text identifier	Present; preserve leading zeros
line_id	Text identifier within an order	Pair with order_id must be unique
ordered_on	Calendar date, YYYY-MM-DD	Valid date; original locale confirmed
quantity	Integer count of units	Positive for this sales-only export
unit_price_gbp	Decimal amount in GBP	Non-negative; currency fixed
dispatched_on	Date or missing	Missing means not yet dispatched

These rules are specific to this example. A returns dataset would need different quantity rules. Never impose a plausible business rule without checking the data's purpose.

4. Separate repairable formatting from uncertain meaning

Received value	Safe next step	Avoid
`00127` in an ID column	Keep as text	Converting it to a number
`03/04/2026`	Confirm day/month convention	Guessing from your computer's locale
Blank dispatch date	Keep missing under the agreed definition	Replacing it with zero or today's date
`North` and `North`	Inspect whitespace and normalize a derived field	Trimming every field, including meaningful text
Two identical rows	Check export history and the row definition	Deleting by appearance alone

Keep uncertain records in an exceptions file with the original value and reason. Quarantining a row is different from proving it is wrong.

5. Validate before and after each transformation

Record row count, distinct key count, missing required fields, invalid dates, and domain violations. For categorical fields, inspect distinct values and frequencies. Rare values deserve review; they are not necessarily errors.

Then reconcile totals. In the fictional export, 120 input rows become 118 accepted rows plus 2 exceptions. All 120 rows are accounted for. If the quantity total changes from 342 to 337, the exception report should explain the missing five units. A reassuring row count alone would miss a changed amount.

For joins, test the key on both sides first. Joining two tables with repeated keys can multiply rows. Compare row counts and totals before and after the join, and count unmatched keys explicitly.

6. Export a handover package

Keep four items together:

The unchanged source file.
The cleaned CSV, with documented encoding and delimiter.
The data dictionary and transformation log.
The exceptions file and reconciliation summary.

CSV does not preserve spreadsheet types, formatting, or multiple tabs. Tell the recipient how to import IDs and dates. For untrusted text that may be opened in spreadsheet software, assess formula interpretation separately; a parser-valid CSV is not automatically safe to open as a spreadsheet.

Reusable completion note

Source and retrieval date:
One row represents:
Unique key:
Input rows / accepted rows / exception rows:
Type, date, unit, and missing-value conventions:
Transformations performed:
Totals reconciled:
Unresolved questions and owner:
Output filename and version:

Ready to hand over when: the next person can reproduce the import, explain each exclusion, and connect a cleaned value to its original record.

Reference and scope

W3C: Model for Tabular Data and Metadata on the Web provides the format and metadata background. The workflow, schema, and reconciliation example here are original practical guidance; all sample records and counts are fictional.

Suggest an edit