daveandClaude Fable 5
c9c90920cc
Sniffs the format on page 1: printed row numbers -> gridded mode (unchanged); UUID-keyed rows -> new headerless mode, which anchors rows on per-record UUIDs and takes the column map from the page-1 header labels. Handles fused cells (ID+Name+Org+networks), mid-value line wraps, two-timestamp Time Frame ranges, wrapped header labels, and per-export schema drift. Validated against three monthly JC PD audits: 120/120 cross-engine spot checks, 0 anomalies, field-identical to the standalone converter it replaces. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_012bQ28wRBiQYcSR9FzV9U7c
99 lines
4.9 KiB
Markdown
99 lines
4.9 KiB
Markdown
# How the reference conversion was validated
|
||
|
||
pinion was developed against a real file: a 131,072-page, 360 MB PDF of a
|
||
law-enforcement network-audit spreadsheet (1,048,576 rows × 14 columns,
|
||
printed from Excel via PDF-XChange on Windows). Every design decision below
|
||
was forced by something that actually went wrong on that file.
|
||
|
||
## Independent checks the output passed
|
||
|
||
1. **Row-number chain.** Excel prints its own row numbers in the left
|
||
margin. The reconstruction reproduces 1..1,048,576 with no gaps and no
|
||
duplicates. Any method that drops or invents rows fails this
|
||
immediately — a competing hand count of the same file reported
|
||
1,081,343 rows, which is exactly one phantom row per page (Trap 3) and
|
||
also exceeds Excel's hard row limit, so it can be rejected on
|
||
arithmetic alone.
|
||
|
||
2. **Cross-engine spot check.** 40 randomly sampled rows were re-extracted
|
||
with pdftotext (Poppler — a completely independent PDF engine) and
|
||
matched on ID, agency, and printed row number: 40/40.
|
||
|
||
3. **Implementation-independent reproduction.** Four separately written
|
||
implementations (a hardcoded-geometry minimal script, a heavily
|
||
commented teaching version, a parallel self-verifying version, and this
|
||
package) produce **byte-identical** CSVs across all 1,048,575 data
|
||
rows, verified with `cmp`.
|
||
|
||
4. **Internal consistency.** Every value in the ID column is a
|
||
well-formed UUID (1,048,575 of 1,048,575); every row has exactly the
|
||
same field count; zero text runs failed to map to a row or column.
|
||
|
||
## Bugs the checks caught during development
|
||
|
||
These are the reasons the self-checks exist. Each was caught only by
|
||
diffing against known-good output or by a check line going red:
|
||
|
||
- **Single-character redactions are ~3.4pt wide** — narrower than some
|
||
grid marks. A size-based bar filter dropped 5 of them. Fix: filter by
|
||
shape (taller than half a row, thicker than a hairline), not by area.
|
||
- **Some blackout bars are filled four-sided paths, not rectangles.**
|
||
Visually identical, different PDF instructions. Inspecting individual
|
||
line segments missed 4 bars, including one 121pt wide. Fix: measure
|
||
whole filled regions, never segments.
|
||
- **Two redaction bars cover the printed row number itself**, making the
|
||
gutter unreadable on those pages. Anchoring rows solely on printed
|
||
numbers silently dropped both rows. Fix: anchor on geometry, use the
|
||
printed numbers as a cross-check (`printed numbers disagreed` must be 0).
|
||
- **A one-character join bug** (`" ".join` vs `"".join`) corrupted the 191
|
||
cells whose text the PDF drew in multiple pieces. Caught by byte-diff.
|
||
|
||
## Known artifacts in the source data (not conversion errors)
|
||
|
||
- 19 rows contain an unmapped `ff` ligature (the subset font has no
|
||
Unicode mapping for it): `Sex O�enses`, `Tra�ic`. Exact-match filters on
|
||
those strings will miss these rows.
|
||
- 141 rows contain double-encoded UTF-8 from the source system
|
||
(`100’s`, `–`). Present in the PDF itself.
|
||
- The sheet ends at exactly 1,048,576 rows — Excel's ceiling — with the
|
||
final row fully populated. Comparison of per-day record counts against
|
||
the file's claimed date range shows several end-of-range days thinned or
|
||
absent: the export was truncated at the ceiling, and roughly 17% of the
|
||
original records are estimated to be missing. All totals from the file
|
||
are floors.
|
||
|
||
# Headerless mode (v1.1.0)
|
||
|
||
Validated against three monthly Johnson City TN PD audit PDFs printed via
|
||
"Microsoft Print to PDF" without headings (48/74/69 pages; 2,032 / 2,437 /
|
||
2,607 rows).
|
||
|
||
Checks passed on all three:
|
||
|
||
1. **Cross-engine spot check.** 40 random rows per file re-verified
|
||
against pdftotext: 120/120 after making the probes wrap-tolerant (a
|
||
cell that wraps mid-value in the PDF never appears as one contiguous
|
||
string in any text extractor's output — probe fragments, not lines).
|
||
2. **Range sanity.** Every two-timestamp Time Frame parses and runs
|
||
start <= end (7,074 ranges, 0 disordered).
|
||
3. **Anomaly counters at zero**: no bad UUIDs, no unplaced runs, no
|
||
strays in the Name/Org windows.
|
||
|
||
Bugs the checks caught during development:
|
||
|
||
- **Wrapped header labels** ('Total Networks ' / 'Searched' on stacked
|
||
runs) made a whole column invisible to name-matching; its values landed
|
||
in the Org Name window and were silently overwritten. Fixed by merging
|
||
stacked header runs; guarded by the strays counter.
|
||
- **A false-alarm verification failure**: 43 spot-checks "failed" because
|
||
the probe assumed cells appear contiguously in pdftotext output. Glyph
|
||
level inspection proved the CSV was right and the check was naive.
|
||
Kept here as a reminder: verify the verifier before trusting a red
|
||
result.
|
||
|
||
Schema drift is real: of the three sibling exports, one has all 13
|
||
columns, two lack License Plate entirely, and Total Networks Searched
|
||
moves between its own column and being fused into the ID run. Headerless
|
||
mode takes each file's schema from its own header and reports what's
|
||
absent.
|