How to clean CSV data before import
A safe CSV cleanup workflow that preserves the original file and turns import assumptions into explicit checks.
CSV is a family of conventions rather than one perfectly uniform format. Delimiters, quoting, encoding, decimal marks, dates, and line endings vary between exporters.
Keep the original file unchanged and perform cleanup on a copy. Record every transformation so the import can be reproduced or reversed.
Confirm structure before editing values
Detect encoding, delimiter, quote character, newline style, header presence, and expected column count. Inspect rows from the middle and end, not only the first page.
Look for embedded newlines, escaped quotes, duplicate headers, extra separators, BOM characters, and rows with too many or too few columns.
Normalize with explicit rules
Define how empty strings, nulls, booleans, dates, decimal separators, leading zeros, and whitespace should be represented in the destination system.
Deduplicate using a stable business key rather than the whole row when harmless formatting differences may exist.
Protect and verify the import
Treat cells beginning with =, +, -, or @ as potential spreadsheet formulas when files will be opened in spreadsheet software.
Import a small sample first, compare row counts and rejected rows, then validate identifiers, totals, and a random sample in the destination.
CSV cleanup checklist
- Preserve the original export unchanged.
- Confirm encoding, delimiter, headers, and column count.
- Define null, date, number, and leading-zero rules.
- Deduplicate using an intentional business key.
- Neutralize spreadsheet formulas where applicable.
- Test a sample import and reconcile row counts.
Related guides
Learn the workflow behind this tool and what to check next.
How to debug an API JSON response step by step
A repeatable workflow for separating transport errors, JSON syntax problems, and data-contract failures in API responses.
How to debug JSON API payloads
A practical workflow for formatting JSON, finding syntax errors, validating payload shape, and checking response status when API data looks wrong.