Metadata-Version: 2.4
Name: sheet-lint
Version: 0.1.0
Summary: Audit one CSV or Excel workbook for the quiet defects that make a spreadsheet return a slightly wrong number.
Author: devops-gm88
License: MIT
Project-URL: Homepage, https://github.com/devops-gm88/sheet-lint
Project-URL: Source, https://github.com/devops-gm88/sheet-lint
Keywords: spreadsheet,csv,xlsx,excel,audit,data-quality,data-validation,cli,standard-library
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Financial and Insurance Industry
Classifier: Intended Audience :: Information Technology
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Office/Business :: Financial :: Spreadsheet
Classifier: Topic :: Utilities
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Dynamic: license-file

# sheet-lint

[![CI](https://github.com/devops-gm88/sheet-lint/actions/workflows/ci.yml/badge.svg)](https://github.com/devops-gm88/sheet-lint/actions/workflows/ci.yml)
[![Python versions](https://img.shields.io/badge/python-3.9%20%7C%203.10%20%7C%203.11%20%7C%203.12%20%7C%203.13%20%7C%203.14-blue)](pyproject.toml)
[![License: MIT](https://img.shields.io/badge/license-MIT-black.svg)](LICENSE)

Point it at a CSV or an Excel workbook and it tells you, in ranked order, what
is quietly wrong with it - with the sheet, column and row behind every claim.

Python 3.9+ - **standard library only, zero dependencies** - and no network
access at any point.

```bash
python3 -m pip install git+https://github.com/devops-gm88/sheet-lint.git
```

Not on PyPI yet - the GitHub URL above is the install. It is one console script
with no dependencies behind it.

```console
$ sheet-lint invoices.xlsx
```

## Why this exists

Most spreadsheet problems announce themselves. A deleted tab breaks a formula
with `#REF!`. A bad paste produces `#VALUE!`. A missing file fails loudly.

The expensive ones do not. They are the ones where a number comes out, looks
plausible, and is wrong:

- a `SUM` range that was sized before somebody appended thirty rows, so the
  total is short by exactly those rows and nothing on screen says so;
- a column of amounts where a few values were pasted in as text, so `SUM`
  skips them while `COUNT`, `AVERAGE` and the sort order each disagree with
  the others;
- one invoice key typed twice, so that customer's spend is counted twice in
  one report and once in another;
- two blank rows in the middle of a table, so everything below them fell
  outside the filter, the pivot range and the last paste-down;
- a column headed `Amount` that holds something else entirely;
- an identifier carrying a trailing space, so an exact-match lookup silently
  finds nothing and returns a confident blank.

None of these raise an error. They produce a number that somebody takes to a
meeting.

sheet-lint reads a file the way a careful auditor would - judging each cell
against the whole column it sits in - and reports what it found, worst first,
with a locator for every finding. It is a static analysis pass. It never
modifies your file.

## Install

sheet-lint is not published to PyPI. Install it from a clone:

```console
git clone https://github.com/devops-gm88/sheet-lint.git
cd sheet-lint
python3 -m pip install -e .
sheet-lint path/to/your-workbook.xlsx
```

That installs a `sheet-lint` console script. Nothing else gets installed: the
project declares no dependencies.

To try it without installing anything at all, run it straight from the source
tree:

```console
$ PYTHONPATH=src python3 -m sheet_lint path/to/your-workbook.xlsx
```

Or run it on the fixtures that ship with the repository:

```console
$ sheet-lint tests/fixtures/messy.xlsx
```

## What it looks like

Real output, from the committed fixture `tests/fixtures/duplicates.csv`
(19 records, two repeated invoice keys and one customer entered twice):

```console
sheet-lint 0.1.0
file       tests/fixtures/duplicates.csv  (csv, 1 sheet)
sheet      duplicates         columns A-G | 19 populated row(s) | identifier: 'Invoice ID' (A)

2 findings  (1 high, 1 medium)

[1] HIGH   duplicate_key
    19 populated Invoice ID values collapse to 17 distinct identifiers; 2 rows repeat a key that is already in use
    why       Every total, join and export that reads this column counts the repeated key twice, so one record quietly becomes two.
    where
      duplicates!A8  key 'INV-1004' repeats the value at A5
      duplicates!A15  key 'INV-1011' repeats the value at A13

[2] MEDIUM near_duplicate_records
    1 row pair matches at 90% or better outside the identifier column (case, spacing and punctuation ignored)
    why       Two spellings of one record are two records to every total and every join, so one customer's spend is split across two rows.
    where
      duplicates!A7:G7  96% identical to row 17 outside the identifier column

notes
  - 4 checks apply only to .xlsx and did not run on this file: hidden_rows, range_stops_short, hardcoded_in_formula_column, literal_constant_in_formulas
```

Exit code `1`, because findings exist.

Here is the workbook fixture, `tests/fixtures/messy.xlsx`, trimmed after the
first four findings:

```console
sheet-lint 0.1.0
file       tests/fixtures/messy.xlsx  (xlsx, 2 sheets)
sheet      Orders             columns A-G | 16 populated row(s) | identifier: 'Invoice ID' (A)
sheet      Rates              columns A-C | 5 populated row(s) | identifier: 'Label' (A)

9 findings  (4 high, 4 medium, 1 low)

[1] HIGH   blank_key_values
    1 of 15 records has an empty Invoice ID
    why       A record with no key cannot be looked up or joined, and an export or pivot that keys on this column drops it without saying so.
    where
      Orders!A9  row has data but no Invoice ID

[2] HIGH   duplicate_key
    15 populated Invoice ID values collapse to 14 distinct identifiers; 1 row repeats a key that is already in use
    why       Every total, join and export that reads this column counts the repeated key twice, so one record quietly becomes two.
    where
      Orders!A11  key 'INV-1004' repeats the value at A5

[3] HIGH   range_stops_short
    1 formula range ends above rows that hold values, so those rows are outside the calculation
    why       This is the quiet one: the formula is valid, the result is a number, and it is simply missing the rows below the range, so the sheet understates a total with nothing to indicate it.
    where
      Orders!F14  = SUM(F2:F12) covers F2:F12, but column F holds data through row 17; first value outside the range is F13

    ... (four more findings follow, including:)

[7] MEDIUM hardcoded_in_formula_column
    column F (Amount) is built from formulas in 15 of its 16 populated rows, but 1 cell holds a number typed by hand
    why       The typed cell keeps whatever it was given: when the inputs around it change it stays put, so one row of the column quietly disagrees with the other 15.
    where
      Orders!F7  column F is calculated in 15 of 16 populated rows, but this cell holds the typed value '612.5'


[9] LOW    literal_constant_in_formulas
    the value 1.13 is written into 5 formulas in this sheet instead of being held in one cell
    why       A rate or threshold typed into formulas cannot be found by searching cells, so changing it means changing every formula and missing one has no visible effect.
    where
      Rates!C2  =B2*1.13
      Rates!C3  =B3*1.13
      Rates!C4  =B4*1.13
      Rates!C5  =B5*1.13
      Rates!C6  =B6*1.13
```

A file with nothing wrong with it says so, and exits `0`:

```console
sheet-lint 0.1.0
file       tests/fixtures/clean.csv  (csv, 1 sheet)
sheet      clean              columns A-G | 40 populated row(s) | identifier: 'Invoice ID' (A)

no findings

notes
  - 4 checks apply only to .xlsx and did not run on this file: hidden_rows, range_stops_short, hardcoded_in_formula_column, literal_constant_in_formulas
```

## Exit codes

| Code | Meaning |
| ---- | ------- |
| `0`  | The file was read and nothing was found |
| `1`  | The file was read and at least one finding was reported |
| `2`  | The file could not be read or linted |

`--fail-on {high,medium,low}` sets the lowest severity that produces exit code
`1`, so a pipeline can ignore cosmetic findings:

```console
$ sheet-lint --fail-on high invoices.xlsx
```

## Options

```
usage: sheet-lint [-h] [--header-row N] [--json] [--max-examples N]
                  [--fail-on {high,medium,low}] [--color {auto,always,never}]
                  [--quiet] [--list-checks] [--version]
                  [path]

Audit one CSV or Excel workbook for the defects that make a spreadsheet return
a slightly wrong number: duplicate and near-duplicate records, unusable key
columns, numbers stored as text, rows that are silently excluded, formula
ranges that stop short, and headers that do not match their column.

positional arguments:
  path                  the .csv, .tsv, .txt, .xlsx or .xlsm file to audit
                        (required unless --list-checks is used)

options:
  -h, --help            show this help message and exit
  --header-row N        1-based row holding the column headers (default: 1)
  --json                write the report as JSON instead of text
  --max-examples N      how many locations to list per finding (default: 8)
  --fail-on {high,medium,low}
                        lowest severity that produces exit code 1 (default:
                        low)
  --color {auto,always,never}
                        colour the severity column (default: auto)
  --quiet               print nothing; use the exit code only
  --list-checks         list every check and the formats it runs on, then exit
  --version             show program's version number and exit

exit codes: 0 clean, 1 findings, 2 error. Every finding is reported with the
sheet, column and row or range it came from.
```

| Flag | What it does |
| ---- | ------------ |
| `--header-row N` | Use row `N` as the header row (default `1`). The report says when a title row above the header looks likely. |
| `--json` | Emit the same report as JSON, for a script or a findings spreadsheet. |
| `--max-examples N` | How many locations to list per finding (default `8`). The true count is always reported. |
| `--fail-on` | Lowest severity producing exit code `1` (default `low`). |
| `--color` | `auto` (default, honours `NO_COLOR`), `always`, `never`. JSON is never coloured. |
| `--quiet` | Print nothing; use the exit code only. |
| `--list-checks` | List every check and the formats it runs on. |
| `--version` | Print the version. |

### JSON

`--json` writes one object carrying the same information as the text report,
including every locator:

```json
{
  "tool": "sheet-lint",
  "version": "0.1.0",
  "file": "tests/fixtures/blank_key.csv",
  "format": "csv",
  "sheets": [
    {
      "name": "blank_key",
      "kind": "csv",
      "columns": 7,
      "data_rows": 15,
      "records": 15,
      "first_data_row": 2,
      "last_data_row": 16,
      "identifier_column": {
        "letter": "A",
        "header": "Invoice ID",
        "reason": "column A header \"Invoice ID\" names an identifier",
        "distinct_values": 12
      }
    }
  ],
  "summary": {
    "findings": 1,
    "high": 1,
    "medium": 0,
  ...
}
```

## Use it as a library

The CLI is a thin wrapper over the same code:

```python
from sheet_lint import lint_file

result = lint_file("book.xlsx", header_row=1)

print(result.clean, len(result.findings), result.severity_counts())
for finding in result.findings:
    print(finding.severity.value.upper(), finding.code, "-", finding.summary)
    for location in finding.locations:
        print("   ", location.render())      # e.g. Orders!F14  = SUM(F2:F12) ...
```

## What it checks

21 checks, as listed by `sheet-lint --list-checks`:

```console
sheet-lint 0.1.0 runs 21 checks:
  duplicate_key                 csv, xlsx    Duplicate identifier values
  duplicate_rows                csv, xlsx    Whole rows repeated
  near_duplicate_records        csv, xlsx    Near-duplicate records
  missing_key_column            csv, xlsx    No identifier column
  blank_key_values              csv, xlsx    Records with no identifier
  padded_key_values             csv, xlsx    Identifier values with stray whitespace
  inconsistent_key_format       csv, xlsx    Identifier column with mixed conventions
  key_sequence_gap              csv, xlsx    Gaps in the identifier sequence
  numbers_stored_as_text        csv, xlsx    Numbers held as text
  mixed_types_in_column         csv, xlsx    Mixed types in one column
  rows_after_gap                csv, xlsx    Blank rows with data underneath
  total_row_not_last            csv, xlsx    A total row is not the last row
  hidden_rows                   xlsx         Hidden rows or columns
  blank_header                  csv, xlsx    Columns with data and no header
  duplicate_header              csv, xlsx    Two columns with the same header
  header_row_missing            csv, xlsx    No header row
  header_content_mismatch       csv, xlsx    Header does not match the column
  possible_title_row            csv, xlsx    A title row above the header row
  range_stops_short             xlsx         A formula range stops before the data ends
  hardcoded_in_formula_column   xlsx         A typed value inside a calculated column
  literal_constant_in_formulas  xlsx         A constant typed into several formulas
```

A CSV run is honest about what it skipped: a CSV has no formulas and no hidden
rows, so the `.xlsx`-only checks do not run, and the report says so in its
`notes` section instead of implying a clean bill of health.

### How the checks decide

- **Records are counted on an identifier, not on rows.** sheet-lint picks one
  identifier column per sheet - by header (`Invoice ID`, `Order Number`,
  `SKU`, `Ref`) or, failing that, the leftmost column whose values are all
  distinct. Findings quote figures like "19 populated Invoice ID values
  collapse to 17 distinct identifiers". Identity ignores case, spacing,
  punctuation and leading zeros, so `007` and `7` are one identifier written
  two ways.
- **A sheet with no usable identifier is reported as such**
  (`missing_key_column`) rather than keyed on an arbitrary column.
- **Header checks use a vocabulary of header words.** `Amount`, `Total`,
  `Qty`, `Date`, `Email`, `Invoice ID` and similar words carry an expectation;
  a header sheet-lint does not recognise is left alone instead of guessed at.
- **Near-duplicate records** are compared on everything except the identifier
  column, at 90% text similarity or better, after blocking rows by vocabulary
  overlap and length so that the comparison stays affordable.
- **Every finding carries a locator** - a cell such as `Orders!F14` or a range
  such as `Orders!A14:G17`. The `Finding` type refuses to be built without
  one, so an unlocatable claim cannot reach the report.
- **Excel dates are read through the cell format.** A workbook stores a date
  as a serial number plus a date number format, so `45659` is recognised as a
  date because the cell is formatted as one - which is why `dates.xlsx`, a
  clean workbook of real dates, produces no findings.
- **Nothing is recalculated.** Formulas are read as stored, cached results
  included.

## What it does not do

- **It never changes your file.** It only reads.
- **It does not recalculate formulas.** Cached values are used as stored, so a
  stale cached value is linted exactly as the file holds it.
- **It does not know your business rules.** It cannot tell that a formula
  multiplies the wrong two columns, that a price is out of date, or that a
  total is impossible for your business. It finds structural defects, not
  wrong intentions.
- **It does not read every spreadsheet format.** `.csv`, `.tsv`, `.txt`,
  `.xlsx` and `.xlsm` only - not the legacy binary `.xls`, not `.ods`, not a
  Google Sheets URL, not a password-protected workbook.
- **`.xlsx` support is a defined subset**: cell values, formulas, shared
  strings, hidden rows and columns, and number formats (used to recognise real
  dates and Text-formatted cells). Charts, pivot tables,
  defined names, conditional formatting and data validation are outside it and
  are skipped without complaint. It never fakes a part it did not read.
- **CSV has no types, so type checks work by inference.** A CSV cell is text;
  sheet-lint infers what it looks like and reports what it observed.
- **The near-duplicate scan has a finite pair budget.** On a very large sheet
  the report says when that budget ran out instead of quietly returning a
  short answer.
- **Silence is not certification.** A check that reports nothing found
  nothing; it is not a statement that the column is correct.
- **It stops rather than samples.** A file with more than 50,000 rows, or more
  than 1,024 populated columns in a sheet, is reported as an error (exit code
  `2`) instead of being analysed in part. A partial audit that looks complete
  is worse than no audit.
- **No telemetry, no network, no state.** It reads one file and prints a
  report.

## Tests

The suite uses `unittest` from the standard library and needs no runner:

```console
$ PYTHONPATH=src python3 -m unittest discover -s tests -v
```

= 152 tests, all passing on Python 3.9 and Python 3.14. After
`pip install -e .` the same command runs without `PYTHONPATH`.

The fixtures in `tests/fixtures/` are the suite's evidence, and each one is a
small deliberate case:

| Fixture | What it demonstrates |
| ------- | -------------------- |
| `clean.csv` | 40 tidy records: the tool must find nothing at all |
| `duplicates.csv` | Two repeated keys and one customer entered twice, one word apart |
| `blank_key.csv` | Three records with no identifier, on non-sequential account numbers |
| `trailing_rows.csv` | Two blank rows with six records underneath them |
| `key_gaps.csv` | An unbroken invoice sequence with three numbers missing |
| `key_format.csv` | Identifiers with stray whitespace, and two separator conventions |
| `messy.xlsx` | A workbook with a blank key, a repeated key, numbers stored as text, a typed-over formula cell, a `SUM` that stops five rows short, a hidden row, a misleading header, and a hardcoded rate |
| `dates.xlsx` | Real Excel dates - serial numbers wearing a date format - in a workbook that must be reported clean |

Every fixture is invented. No real workbook contributed to this repository,
and no organisation or person is named anywhere in it.

### Regenerating the fixtures

The `.xlsx` fixture is a real workbook written by a small script that builds
the OOXML parts with `zipfile` and `xml.etree` - no spreadsheet library
involved, because the project has no dependencies:

```console
$ python3 tools/make_fixtures.py
```

The output is deterministic, and a test asserts that the committed fixtures
match what the generator produces byte for byte.

## Layout

```
src/sheet_lint/
  cli.py            argument parsing, exit codes, stream handling
  engine.py         load, run every applicable check, rank the findings
  model.py          Cell, Sheet, Location, Finding, Severity
  infer.py          what a value looks like it is (number, date, email, text)
  profile.py        per-column measurements, identifier column selection
  context.py        the facts handed to each check
  loaders/          csv_loader.py, xlsx_loader.py (zipfile + xml.etree)
  checks/           one module per family of defects
  report.py         ranked plain text, or JSON
tools/
  make_fixtures.py  regenerates tests/fixtures
tests/
  test_*.py         unittest suite, one module per area
```

## License

MIT. See `LICENSE`.
