# 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](https://www.w3.org/TR/tabular-data-model/#best-practice-csv) 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

```text
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](https://www.w3.org/TR/tabular-data-model/) provides the format and metadata background. The workflow, schema, and reconciliation example here are original practical guidance; all sample records and counts are fictional.