csvdoctor

Find out what is wrong with a CSV file, and fix it, in one command.

CSV has no standard. Every export is a small gamble: Excel writes ;-separated Windows-1252 in many countries, databases dump UTF-16, web tools add invisible byte-order marks, and somebody always sorts the spreadsheet and leaves an empty column behind. csvdoctor tells you exactly what is wrong and writes a clean, UTF-8, comma-separated copy, without touching your original.

• Zero dependencies. Pure Python standard library. Works anywhere Python 3.9+ does.
• Honest. It re-checks its own output and tells you what it could not fix.
• Safe by default. It never silently deletes rows or blanks values. Anything destructive is opt-in.
• Usable everywhere. CLI, Python library, JSON output for scripts, and a pre-commit hook for CI.

Install

pip install git+https://github.com/jasonkeanuchristy1-cyber/csvdoctor.git
# or, to keep it out of your project environments:
pipx install git+https://github.com/jasonkeanuchristy1-cyber/csvdoctor.git

Quick start

$ csvdoctor check examples/messy.csv
examples/messy.csv
  Encoding ........ Windows-1252
  Delimiter ....... semicolon
  Line endings .... CRLF
  Size ............ 9 data rows x 8 columns

  Found 15 issues (3 errors, 10 warnings, 2 info)

  ERROR   RAGGED_ROWS  Wrong number of fields in 1 row (the header has 8): 1 too short.
            rows: 7
            -> fixed by `csvdoctor fix`

  ERROR   EXTRA_DATA  Real data beyond the last header column in 1 row. This usually means an
                      unquoted delimiter inside a value (e.g. a comma in 'Smith, John').
            rows: 8
            -> needs manual review
  ...

(The full report is abridged here; examples/messy.csv is a real, deliberately broken file you can try it on.)

Now fix it:

$ csvdoctor fix examples/messy.csv --dedupe --normalize-nulls --neutralize-formulas
Fixed examples/messy.csv -> examples/messy.fixed.csv
  - Re-encoded from Windows-1252 to UTF-8
  - Changed delimiter from semicolon to comma
  - Trimmed whitespace in 2 cells
  - Repaired garbled text in 1 cell
  - Removed 1 empty row
  - Padded 1 short row with empty fields
  - Dropped 1 empty column: "Notes"
  - Emptied 1 placeholder value (N/A, #REF!, ...)
  - Prefixed 1 formula-like cell with an apostrophe
  - Renamed duplicate columns: "Email" -> "Email_2"
  - Removed 1 duplicate row

Still needs attention (4):
(row numbers below refer to the fixed file)
  ERROR   EXTRA_DATA  ...
  WARNING EXCEL_SCI_NOTATION  ...
  WARNING MIXED_TYPES  ...
  WARNING INCONSISTENT_DATES  ...

The four leftovers are things no tool can safely decide for you: a row with a stray delimiter, IDs that Excel already rounded, a 12o typed instead of 120, and dates that could be day-first or month-first. csvdoctor shows you where they are and leaves the data alone.

What it checks

"fix" means the default csvdoctor fix handles it. --flags are opt-in because they change or remove data: for example NA is a real value in some data sets (Namibia's country code), so blanking placeholders is never done unless you ask.

Command reference

csvdoctor check FILE [FILE ...]   report problems, change nothing
csvdoctor fix FILE                write a cleaned copy

Use - as the file name to read from standard input.

check

fix

Progress messages go to stderr, so csvdoctor fix data.csv -o - | other-tool is safe.

Exit codes

Use it in CI and pre-commit

Fail a pull request when a committed CSV is broken:

# .github/workflows/csv.yml
- run: pip install git+https://github.com/jasonkeanuchristy1-cyber/csvdoctor.git
- run: csvdoctor check data/*.csv --fail-on error

As a pre-commit hook:

repos:
  - repo: https://github.com/jasonkeanuchristy1-cyber/csvdoctor
    rev: v0.1.0
    hooks:
      - id: csvdoctor

Machine-readable output for scripts and dashboards:

csvdoctor check export.csv --format json | jq '.files[0].summary'

Use it as a library

from csvdoctor import read_table, diagnose, repair

raw = open("export.csv", "rb").read()

report = diagnose(read_table(raw))
for issue in report.issues:
    print(issue.severity, issue.code, issue.message)

fixed = repair(raw, dedupe=True)      # -> Repaired(data=bytes, actions=[...], ...)
open("clean.csv", "wb").write(fixed.data)
print("\n".join(fixed.actions))

Design principles

1. Never lose data silently. Dropping rows and blanking values are opt-in. Rows that cannot be repaired safely are left untouched and reported.
2. Don't guess when it matters. 03/02/2024 could be 3 February or 2 March, so csvdoctor reports mixed date formats instead of "fixing" them.
3. Verify, don't claim. After fix, the result is re-parsed and re-checked, and what remains is shown to you.
4. Idempotent. Running fix on its own output changes nothing. This is covered by randomised tests.

Limitations

• The whole file is loaded into memory. Files up to a few hundred MB are fine.
• Speed is roughly 50,000 rows per second for check on a typical 8-column file (pure Python, no dependencies).
• Encoding and delimiter detection are heuristics. If it guesses wrong, pass --encoding / --delimiter. Windows-1252 and Latin-1 cannot be told apart with certainty, and csvdoctor assumes Windows-1252 whenever the bytes allow it.
• It does not validate data against a schema (for that, see Frictionless) and does not infer or convert types.

Contributing

Bug reports with a small sample file are the most valuable contribution: real-world messy CSVs are exactly what makes this tool better. See CONTRIBUTING.md for setup and how to add a new check.

License

MIT