CSV and spreadsheets
How to clean CSV data without losing important values
Use a controlled cleanup sequence for delimiters, headers, blank rows, whitespace, duplicates, dates, and inconsistent values.
Document summary
A practical CSV cleanup workflow that protects the original file, applies one transformation at a time, and verifies the export before import.
Key takeaways
- Define the expected columns and data rules before cleaning.
- Normalize structure before comparing values or removing duplicates.
- Keep the original file and verify the cleaned export in the destination system.
Define the data contract before changing rows
CSV cleanup is safest when you know what a valid record should contain. Write down the required headers, expected delimiter, mandatory fields, accepted date format, and whether blank values are allowed.
Without a target structure, a cleanup rule can remove useful distinctions. For example, an empty middle name may be valid while an empty customer identifier may make the row unusable.
- Record the required column names and their order.
- Identify fields that must be unique.
- Decide how null, blank, and zero values should differ.
- Choose a standard date and decimal format.
Inspect the source file before cleaning
Open a representative sample and confirm the delimiter, quote handling, line endings, encoding, and header row. A file named CSV may use commas, semicolons, tabs, or pipes.
Look for rows with a different number of fields, unexpected quotation marks, embedded line breaks, or a byte order mark at the beginning of the first header.
| Check | Why it matters | Typical action |
|---|---|---|
| Delimiter | Controls where fields split | Detect or select comma, tab, semicolon, or pipe |
| Header row | Defines field names | Confirm one header and remove repeated headers |
| Encoding | Affects accented and non-Latin text | Prefer UTF-8 when the destination supports it |
| Row width | Reveals broken quoting or missing values | Review rows with too many or too few fields |
Apply cleanup rules in a safe order
Apply one meaningful transformation at a time so you can identify which rule changed the data. A reliable order is structural repair, whitespace cleanup, value normalization, duplicate review, and final validation.
- 1
Save an untouched copy of the original file.
- 2
Remove fully empty rows and repeated header rows.
- 3
Trim leading and trailing whitespace without changing internal spacing.
- 4
Normalize case, dates, and boolean values only where the business rule is clear.
- 5
Review duplicates using a defined key.
- 6
Export and compare row counts before import.
Normalize values without changing their meaning
Normalization should make equivalent values consistent, not force unrelated values into the same form. Apply case changes to controlled fields such as country codes or status values, not automatically to names and free text.
name,status,joined
Alice ,ACTIVE,7/1/26
alice,active,01-07-2026name,status,joined
Alice,active,2026-07-01
alice,active,2026-07-01Review duplicates after normalization
Duplicate detection works best after whitespace and case rules are consistent. Define whether a duplicate means an identical row or a repeated key such as email, invoice number, or order identifier.
Do not automatically keep the first row. Decide whether the newest, most complete, or highest-priority record should win.
- Compare row counts before and after removal.
- Keep an audit list of removed rows.
- Review blank keys separately.
- Use multiple columns when one field is not unique enough.
Verify the cleaned export
Open the exported file again and confirm that it parses with the expected delimiter and encoding. Check required fields, row counts, a sample of changed values, and the destination import preview.
- 1
Compare original and cleaned row counts.
- 2
Inspect the first, middle, and final rows.
- 3
Search for empty required fields.
- 4
Test the file in a staging or preview import.
- 5
Keep the cleanup settings with the project for repeatability.
