Large Spreadsheet Analyzer

A local, offline desktop tool (GUI + CLI) that scans very large spreadsheet files — 500 MB, 800 MB, 1 GB and more — and finds rows matching user-defined rules ("bad rows to fix"). Supported input formats: CSV, XLS, XLSX, ODS. Nothing ever leaves your machine.

Highlights

• Single streaming pass: the file is read once and every rule is evaluated on each row. A 1M-row / ~140 MB CSV scans in a few seconds with a flat memory profile (matches are stored in SQLite on disk, not in RAM).
• Rules: flat groups of conditions (is_empty, not_empty, equals_column — two cells of the same row — and is_duplicate — the same value(s) appearing in several rows) combined with AND/OR, referencing columns by letter (C), 0-based index (2) or header name (email). Rules are saved/loaded as versioned JSON (see docs/rules.example.json).
• Duplicate detection: is_duplicate takes 1..n key columns (the GUI edits up to 2; more via JSON); matched rows are shown and exported grouped — all rows sharing the same key together. Within each key group, rows are further sub-grouped by the content of the cells to the right of the key columns: one sub-group per identical right-hand content, then a final "unique" set for rows whose right-hand cells match no other row (the report and CSV exports carry a Group column showing this). Blank cells count as equal content; add a not_empty condition to the rule to exclude them. The duplicate index is built on disk (SQLite), never in RAM.
• GUI: a six-step CustomTkinter wizard (file → import options → rules → run → report → duplicate file) translated into English, French, German, Spanish and Portuguese (pt-PT). Scans run in an isolated scan process with live per-rule counters, a global progress bar and a Cancel button (partial results are kept and marked as partial). If the scan process dies (e.g. stopped by the OS), the GUI reports it instead of hanging or crashing.
• Duplicate file generation (step 6): pick a reference file, map its columns to the scanned file, and extract a CSV of duplicate rows to remove. In each duplicate group, rows with identical right-hand cells keep only the lowest ID (the others go to the duplicate file); rows with unique right-hand cells are kept only when an identical row (over the mapped columns) exists in the reference file. The extraction streams in its own process and the result is saved wherever you choose.
• Verification viewer (step 7, entered automatically after saving): every extracted row is re-checked against the original file and the reference — condition A (its content appears several times in the original, with the matching row indices and first cells listed) or condition B (appears exactly once and is missing from the reference); anything else is flagged INVALID. The viewer paginates (selectable or custom page size), jumps to a row or page, and live-filters on the first two columns.
• CLI: the same engine headless, for pipelines (exit code 0 = no matches, 1 = matches found, 2 = error).
• Report: per-rule match counts with lazily loaded sample rows (Previous/Next paging over all matches), CSV export per rule or for all rules, JSON report summary.

Format caveats

• CSV is streamed row by row; row previews later seek() directly to the stored byte offset. (For encodings where byte offsets are unreliable — UTF-16/32, CR-only line endings — matched rows are cached in SQLite instead.)
• XLSX is streamed with openpyxl in read-only mode (constant memory).
• XLS/ODS cannot be read row-by-row: python-calamine materializes the sheet in memory. The app warns when opening large XLS/ODS files — convert to CSV or XLSX for best results.
• Malformed CSV: a single field is capped at 1 MB, so a stray opening quote in a broken file cannot swallow gigabytes into memory. Records that cannot be parsed are skipped, counted, and reported ("N malformed rows skipped"); set the quote character to empty in the import options if your file uses no quoting at all.
• On Linux the temporary result database is kept in /var/tmp (disk) rather than /tmp, which is usually RAM-backed tmpfs — so scans with tens of millions of matches don't consume RAM.

Development

Requires Python 3.11+ and uv:

uv sync                 # create venv + install deps
uv run pytest           # unit tests
uv run pytest -m gui    # GUI smoke tests (needs a display; use xvfb-run on headless Linux)
uv run pytest -m perf   # 1M-row performance smoke test
uv run ruff check       # lint
uv run lsa --help       # CLI
uv run lsa-gui          # GUI

CLI examples

lsa run --file data.csv --rules rules.json --separator ";" --encoding utf-8 \
        --report report.json --export-matches out_dir/ --sample 5
lsa validate-rules rules.json

Generate a large test file

uv run python scripts/generate_fixture.py --rows 1000000 --out big.csv

Packaging

Single-file executables (every library bundled into one binary per front end):

uv sync --group build
uv run python scripts/build_bundle.py
# -> dist/bundle/lsa-gui-<os>-<arch>[.exe] and lsa-<os>-<arch>[.exe]

Folder build (faster startup, used for releases):

uv run pyinstaller packaging/lsa.spec --noconfirm   # output in dist/lsa/

--collect-all customtkinter (done inside the specs) is required — without it the CustomTkinter theme assets are missing at runtime. A Linux binary runs on distributions whose glibc is at least as new as the build machine's; CI builds on the oldest available runner (Ubuntu 22.04) for wide coverage.

CI/CD

• ci.yml: ruff + pytest on Ubuntu/Windows/macOS × Python 3.11/3.12, on every push and pull request.
• build.yml: on every pushed branch, single-file executables for Linux and Windows are built and uploaded as workflow artifacts (GitHub → Actions → the run → Artifacts).
• release.yml: on v* tags, PyInstaller folder builds for Windows, macOS and Linux are attached to the GitHub release (Windows is the priority target).

Architecture

src/lsa/core/   file readers (RowStream), rules, evaluation, SQLite result
                store, report model, JSON (de)serialization, i18n catalogs
src/lsa/cli/    argparse front end
src/lsa/gui/    CustomTkinter wizard; testable presenters; scans run in a
                spawned subprocess (Tcl/Tk is not thread-safe — process
                isolation makes worker-related crashes impossible), messages
                polled from a queue with after() on the main thread