---
name: processing-csv
description: Creates, reads, modifies, and converts CSV and TSV files — filtering rows, adding or renaming columns, changing delimiters, and converting to or from JSON. Use when the user asks to read, write, edit, filter, sort, clean, split, or convert a .csv or .tsv file, mentions comma- or tab-separated data, or asks to turn CSV into JSON or JSON into CSV. Do not use for data-quality profiling or auditing a CSV (a separate profiling-csv-data skill covers that) and not for .xlsx Excel files.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Processing CSV

## When to use / when NOT to use
- **Use for:** any task where a `.csv` or `.tsv` file is the input or output — creating, editing, filtering, converting.
- **Do NOT use for:** profiling/auditing data quality (use `profiling-csv-data`) or `.xlsx` Excel files (use `processing-xlsx`).

## Quick reference — one default per operation

**Read — stdlib `csv`, always `newline=""`:**
```python
import csv
with open("in.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.DictReader(f))        # rows as dicts keyed by header
```

**Write:**
```python
with open("out.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=["id", "name"])
    w.writeheader()
    w.writerows(rows)
```

**Unknown delimiter — sniff it:**
```python
with open("in.csv", newline="", encoding="utf-8") as f:
    dialect = csv.Sniffer().sniff(f.read(4096))   # 4 KB is plenty to detect the delimiter
    f.seek(0)
    rows = list(csv.DictReader(f, dialect=dialect))
```

**TSV:** same code with `delimiter="\t"` passed to the reader/writer.

**Escape hatch — pandas** for large files (>~1 GB) or typed/numeric operations:
```python
import pandas as pd                        # pip install pandas
df = pd.read_csv("in.csv", dtype=str)      # dtype=str avoids silent type coercion
```

## Rules
- Always open CSV files with `newline=""` — omitting it doubles line breaks on Windows.
- The `csv` module quotes fields containing delimiters/quotes/newlines automatically — never hand-assemble CSV with string joins.
- Keep the header row unless the user explicitly wants it gone.
- Modify via read-all → transform → write-new (or temp file + `os.replace` to edit in place); never patch a CSV with regex.

## Workflow
1. Read the input with `DictReader` (sniff the dialect if the delimiter is unknown).
2. Transform in Python (filter/map on the list of dicts).
3. Write the result with `DictWriter`, explicit `fieldnames`.
4. **Validate:** re-open the output, check the header matches `fieldnames` and the row count equals what the transform should produce. Fix and rewrite if not.
5. Report output path, row count in → row count out, and columns changed.

## Edge cases & failure modes
- **Missing dependency** → only pandas is third-party: `pip install pandas`.
- **Malformed row (wrong field count)** → `DictReader` puts extras under `None`/fills missing with `None`; count such rows, report them, and ask before dropping — don't silently discard data.
- **Non-UTF-8 file** → `UnicodeDecodeError`; retry with `encoding="latin-1"` and tell the user which encoding was used.
- **Huge file** → stream row-by-row (iterate the reader, write as you go) instead of `list(...)`; or use pandas with `chunksize`.
- **Empty file / header-only** → report it plainly; output a header-only file if a transform was requested.
- **Values with leading zeros or big IDs** (postal codes, phone numbers) → keep them as strings; that's why the pandas escape hatch uses `dtype=str`.

## References
Deeper recipes (filter/sort/dedupe, column ops, csv↔json, delimiter conversion, gotchas): see [references/recipes.md](references/recipes.md).