# csvsleuth

Ask questions of CSV, Excel and JSONL files in plain English. csvsleuth loads your files into an in-memory-style SQLite database, sends only a compact schema card (never your data) to Claude Haiku 5.5, runs the SQL it writes inside a read-only sandbox, and shows the result as a table, optionally with a terminal bar chart and a plain-English explanation of the query.

## Features

- Loads CSV/TSV (delimiter and encoding sniffing), XLSX (every sheet becomes a table) and JSON lines.
- Type inference per column: integers, floats, thousands separators including Indian grouping (`₹1,23,456.78`), dates in many formats with dd/mm vs mm/dd resolved from the values, yes/no/true/false booleans, leading-zero IDs kept as text.
- Multiple files become multiple tables, with join hints from shared column names.
- Compact schema card: types, 3 samples per column, ranges, all values of low-cardinality columns, row counts.
- Safe execution: read-only database, SQLite authorizer (SELECT/READ/FUNCTION only), one statement, time limit, injected LIMIT.
- One automatic repair round when the SQL fails (the error is sent back to the model).
- `--chart`, `--sql`, `--export out.csv`, `--dry-run`, `--verbose` (tokens and cost), on-disk response cache.
- `csvsleuth shell`: REPL with readline history where follow-ups see previous SQL.

## Install

```bash
uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]'
export ANTHROPIC_API_KEY=sk-ant-...   # or: ant auth login
```

## Quickstart

No key needed for these (dry run and the fixture-backed demo model):

```bash
.venv/bin/python examples/make_examples.py        # optional: regenerate the sample data
.venv/bin/csvsleuth examples/sales.csv examples/customers.xlsx "top 5 customers by revenue in Q3" --dry-run
.venv/bin/csvsleuth examples/sales.csv examples/customers.xlsx "top 5 customers by revenue in Q3" \
    --fixtures examples/fixtures.json --sql --chart
```

With a real key (calls Claude Haiku):

```bash
.venv/bin/csvsleuth examples/sales.csv "which product has the highest average discount?" --sql --verbose
.venv/bin/csvsleuth shell examples/sales.csv examples/customers.xlsx examples/expenses.csv
```

## Usage

```
csvsleuth FILE [FILE...] "question" [options]
csvsleuth shell FILE [FILE...] [options]

  --sql          print the generated SQL
  --chart        unicode bar chart (needs one label column and one numeric column)
  --export PATH  write the full result (up to 100,000 rows) to CSV
  --limit N      rows to display (default 50)
  --timeout S    query time limit in seconds (default 5)
  --dry-run      show system prompt, schema card and estimated tokens; no API call
  --no-cache     skip the response cache
  --model ID     default claude-haiku-5-5 (or env CSVSLEUTH_MODEL)
  --fixtures F   answer from a fixture file instead of the API
  --verbose      token usage and estimated cost
```

### Dry run (real output, no key needed)

```text
USER
SCHEMA
TABLE "sales" (5000 rows)
  order_id TEXT e.g. ORD-00001, ORD-00002, ORD-00003; 5000 distinct
  order_date DATE e.g. 2024-02-07, 2024-03-08, 2024-05-25; range 2024-01-01..2024-12-31; 366 distinct
  customer_id INTEGER e.g. 17, 24, 46; range 1..60; 60 distinct
  product TEXT values: Backpack | Desk Lamp | Headphones | Keyboard | Notebook | Water Bottle
  quantity INTEGER e.g. 2, 1, 4; range 1..6
  unit_price INTEGER e.g. 449, 2499, 899; range 120..2499
  discount_pct INTEGER e.g. 15, 0, 5; range 0..15
  revenue REAL e.g. 763.3, 2499.0, 3056.6; range 102.0..14994.0; 144 distinct

TABLE "customers" (60 rows)
  customer_id INTEGER e.g. 1, 2, 3; range 1..60; 60 distinct

```

### Answer with the fixture-backed demo model (real output)

Fixtures are canned model answers (`examples/fixtures.json`), so this exercises the loader, sandbox, table and chart for real while the SQL comes from the file. The sections below that use `--fixtures` do not call any API.

```text
$ csvsleuth examples/sales.csv "month-over-month revenue growth" --fixtures examples/fixtures.json --chart --limit 4
┏━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ month   ┃      revenue ┃ growth_pct ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ 2024-01 │ 1,680,283.60 │            │
│ 2024-02 │ 1,516,847.35 │      -9.70 │
│ 2024-03 │ 1,867,633.50 │      23.10 │
│ 2024-04 │ 1,576,956.80 │     -15.60 │
└─────────┴──────────────┴────────────┘
4 row(s) shown (more available; raise --limit or use --export)
revenue by month
2024-01 │███████████████████████████████████▉ 1,680,283.60
2024-02 │████████████████████████████████▍ 1,516,847.35
2024-03 │████████████████████████████████████████ 1,867,633.50
2024-04 │█████████████████████████████████▊ 1,576,956.80

Explanation Totals revenue per calendar month and compares each month with the previous one as a percentage.
```

Indian number formats and dd/mm/yyyy dates in `expenses.csv` load as REAL and DATE:

```text
$ csvsleuth examples/expenses.csv "how much was spent on travel each month" --fixtures examples/fixtures.json --limit 3
note: expenses.date: dates read as dmy
┏━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ month   ┃ travel_spend ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2024-01 │    90,374.57 │
│ 2024-02 │    31,235.02 │
│ 2024-03 │   122,108.35 │
└─────────┴──────────────┘
```

### Self-repair

The "typo demo" fixture first answers with a nonexistent column; csvsleuth sends the SQLite error back and the fixture's `repair_sql` is used:

```text
$ csvsleuth examples/sales.csv "typo demo" --fixtures examples/fixtures.json --limit 3

Explanation Totals revenue per product.
(first query failed; repaired automatically)
```

### Sandbox in action

Hostile model output (DROP, multiple statements, PRAGMA, runaway recursion) is refused. Real output from a fixture file containing those queries:

```text
The query could not be run (even after one repair): only SELECT queries are allowed
The query could not be run (even after one repair): multiple statements are not allowed
The query could not be run (even after one repair): query exceeded the 1s time limit
```

### Shell

```text
$ csvsleuth shell examples/sales.csv examples/customers.xlsx
sleuth> top 5 customers by revenue
sleuth> only the ones in the South     # sees the previous SQL as context
sleuth> .sql     # last SQL     .schema     .help     .quit
```

## Configuration

| Setting | How |
| --- | --- |
| API credentials | `ANTHROPIC_API_KEY` or an `ant auth login` profile |
| Model | `--model` or `CSVSLEUTH_MODEL` (default `claude-haiku-5-5`) |
| Cache directory | `CSVSLEUTH_CACHE` (default `~/.cache/csvsleuth`) keyed by sha256 of model, system prompt and messages |
| Shell history | `~/.csvsleuth_history` |

Cost: Haiku 5.5 is $0.10 / 1M input and $0.50 / 1M output tokens; a typical question is a few hundred input tokens (a fraction of a cent). `--verbose` prints the exact numbers; cache hits are free.

## Architecture

| Module | Role |
| --- | --- |
| `inference.py` | number/date/boolean inference, date-order resolution, name sanitisation |
| `loader.py` | file readers, `Table`, temp-file `Database` reopened read-only |
| `schema.py` | schema card and join hints, size bounded |
| `sandbox.py` | statement scanner, authorizer, timeout, LIMIT injection |
| `llm.py` | prompt, Haiku call with JSON schema output, cache, usage/cost |
| `engine.py` | ask, execute, repair once, session history |
| `chart.py` | unicode bar chart |
| `fixtures.py` | fixture-backed fake client for offline demos |
| `cli.py` | click CLI, rendering, shell |

See [docs/design.md](docs/design.md) for the algorithms with worked examples.

## Tests

```bash
.venv/bin/pytest -q
```

All tests use a fake client; no network or key is needed.

## Limitations

- Live Claude calls were not exercised in development (no API key available); the request shape follows the Haiku guide and is covered by fake-client tests.
- Ambiguous dd/mm dates default to day-first when no value in the column disambiguates (a note is printed).
- European number format (`1.234,56`) is not recognised and stays text.
- Whole files are held in memory; intended for files up to a few hundred MB at most.
- Model-written SQL can still be wrong; use `--sql` and the explanation to check it.