Authoring templates¶
A template is a YAML file that declares the entities crease should extract
from an xlsx, where each one lives, and what its fields mean. The
templates under test_cases/
in the repository double as worked examples for each layout pattern.
Placeholder page
This guide is being filled out. For now, the README and test_cases/ are the canonical reference.
Skeleton¶
template_id: orders
description: Order export from acme.
entities:
- name: order
cardinality: many # one | many
locate:
tab: Orders
orientation: flat # flat | property_sheet | anchored
header_row: 0
fields:
- name: order_id
source_column: order_id
type: string
pattern: ^ORD-\d{4}$
See the Reference > Template page for the full schema with every field documented.
Versioning¶
A template's version: field gates which grammar features are
recognised at load time. Today there are two values:
version: 1(the default) — the original grammar:entities:,locate:,filename_pattern:, etc.version: 2— adds the top-levelblocks:declaration and theEntity.block:reference field for repeating sections within a tab. See Repeating sections (blocks:).
Loaders reject blocks: declarations under version: 1 rather than
silently ignoring them, so an older runtime never produces
half-extracted output against a v2 template.
Disambiguating duplicated headers¶
If two header cells in the same row carry the same normalized text, a
field with source_column: "DATE" is ambiguous — there are two columns
that could match. Crease emits a header_duplicated warning and binds
to the first occurrence so extraction still proceeds. To bind a specific
field to a specific occurrence, set source_column_index: (0-indexed
across the matches in the header row):
fields:
- name: open_date
source_column: "DATE"
source_column_index: 0 # first DATE column
type: date
- name: close_date
source_column: "DATE"
source_column_index: 1 # second DATE column
type: date
Without source_column_index, both fields would bind to the same column
and report.errors() would contain a header_duplicated entry.
Skipping rows during extraction¶
When a worksheet interleaves data with marker, subtotal, or grand-total
rows that have the same column geometry as real records, use
locate.skip_row_if to drop them before extraction. Each list entry is
a LocateSkipRule; if any one matches, the row is silently omitted
from the canonical output (no row error). This is the top-level Locate
filter; it is distinct from the block-scoped SkipRowRule (which uses
single-column patterns inside a block instance, see
blocks).
locate:
tab: Orders
orientation: flat
header_row: 0
skip_row_if:
# subtotal rows: blank discriminator column
- all_blank: [customer]
# day-of-week marker rows
- column: label
value_pattern: "^(MONDAY|TUESDAY|WEDNESDAY|THURSDAY|FRIDAY|SATURDAY|SUNDAY)$"
# grand-total row: blank discriminator AND populated total
- all_blank: [site]
non_blank: [head_count]
Predicates compose by AND on the same rule (a rule with both
all_blank and non_blank matches when both lists are satisfied) and
by OR across rules. value_pattern is a regex full-matched against the
stringified cell value; combine it with column: to pin a single
column.
Disambiguating anchored labels¶
When a worksheet stacks two cover-sheet-style blocks side by side and
both carry the same labels (a "REPORTING" block in column A and a
"BILLING" block in column D, each with its own Company: /
Email: rows), an anchor whose label_match: "Company:" would
default to the first hit. Two optional fields scope the search:
column: int— restrict the scan to a single 0-indexed column.nth: int— pick the Nth match (1-indexed; default 1).
fields:
- name: reporting_company
type: string
anchor: { label_match: "Company:", column: 0, value_at: right, offset: 1 }
- name: billing_company
type: string
anchor: { label_match: "Company:", column: 3, value_at: right, offset: 1 }
- name: section_two_carrier
type: string
anchor:
label_match: "SHIPPING INFORMATION"
nth: 2 # the second occurrence of the label
value_at: right
offset: 2
Time fields¶
type: time accepts native datetime.time cells, datetime cells (the
time component is extracted), and string cells. Strings are normalized
so that a.m. / p.m. markers match %p (i.e. "7:30 a.m." becomes
"7:30 AM") and then parsed against an optional list of
time_formats:
fields:
- name: collection_time
source_column: "collection_time"
type: time
time_formats:
- "%I:%M %p"
- "%H:%M"
If no time_formats are given, ISO-format strings ("09:30:00",
"09:30") still parse. The canonical value is always the ISO string
form of the time (mirroring how date / datetime fields project).
Regex null collapse¶
When a worksheet uses patterned placeholders for missing values
([Company], [Email], [Fax]), enumerating each variant in
null_tokens is brittle. null_patterns accepts a list of full-match
regexes layered the same way as null_tokens — field overrides
template overrides nothing:
null_patterns:
- "^\\[.+\\]$" # any bracketed placeholder
- "^TBD\\s*\\d*$" # TBD, TBD1, TBD-2, ...
entities:
- name: row
locate: { tab: Sheet1, orientation: flat, header_row: 0 }
fields:
- { name: email, source_column: "email", type: email, nullable: true }
Stopping mid-tab on a pattern¶
data_ends_at already supports an exact-string value_match. When
the sentinel row's text varies in trivial ways (double-space, trailing
colon, capitalization), use value_pattern with a regex:
Free-text banner rows¶
Some reports carry occasional free-text "annotation" rows interleaved
with data — -- REVISED --, dividers, notes in column A. Use
row_is_annotation_if with only_columns_populated: N to drop any
row where N or fewer columns are populated, before field coercion runs:
locate:
tab: Sheet1
orientation: flat
header_row: 0
row_is_annotation_if:
- only_columns_populated: 1
Forward-filling grouping columns¶
Schedules and roll-ups often set a grouping column (period, region,
category) on the first row of a group and leave it blank on the
continuation rows. locate.forward_fill: [col, ...] propagates the
last non-blank value of each listed column down through blank rows
before field coercion runs:
locate:
tab: Sheet1
orientation: flat
header_row: 0
forward_fill: [period, region]
fields:
- { name: period, source_column: "period", type: string }
- { name: region, source_column: "region", type: string }
- { name: units, source_column: "units", type: string }
A continuation row with [None, None, "5-6", 50] inherits period /
region from the row above; the moment a non-blank value appears, it
becomes the new "last seen" value for that column.
Per-row enrichment from a labeled cell¶
When a header block above the data carries a value that should be
attached to every row (an effective date, a reporting period, a
single-cell metadata block), declare it as an enrich entry with
source: anchor:
entities:
- name: order
cardinality: many
locate:
tab: Sheet1
orientation: flat
header_row: 2
enrich:
- field: effective_date
source: anchor
label_match: "EFFECTIVE DATE:"
value_at: right
offset: 1
type: date
fields:
- { name: customer, source_column: "customer", type: string }
label_match / match_mode / value_at / offset mirror the field-
level anchor block. The matched neighbor value is coerced via
type: (so a date cell projects to its ISO string, matching the
rest of the canonical document).
Constraining the anchor's neighbor with value_type¶
anchor.value_type adds a shape check on the matched cell that's
distinct from the field's own type coerce: a value_type mismatch
means the anchor pointed at the wrong cell, not that the value is
the right cell shaped wrongly. The error code is
anchor_value_type_mismatch.
fields:
- name: project_id
type: integer
anchor:
label_match: "Project ID:"
value_at: right
offset: 1
value_type: integer
When the neighbor of Project ID: is a string like "Quarterly Sample
Plan", the report carries a single anchor_value_type_mismatch
entry instead of the harder-to-route wrong_type.
Single-tab workbooks with dynamic tab names¶
Some reports are single-tab workbooks where the operator names the tab
after the period (4-20-26, Week 17). A template tab: ".*" works
but is brittle; the sentinel tab: only binds to the lone non-ignored
sheet regardless of its name:
locate:
tab: only # the workbook must have exactly one non-ignored tab
orientation: flat
header_row: 0
When the workbook has zero or more than one non-ignored tab, tab:
only matches nothing and the existing missing_tab / entity_missing
errors fire.
Inspecting normalized headers¶
When a template hits header_mapping_failed and the cause isn't
obvious, crease.inspect_headers(file, tab=..., header_row=...)
returns the normalized header → column-index map crease actually
sees after NBSP / whitespace collapse / lower-case. Use it instead of
launching a separate REPL to inspect the file.
import crease
crease.inspect_headers("report.xlsx", tab="Sheet1", header_row=0)
# {"customer name": 0, "total items": 1, "due date": 2}
Intentional cross-tab repetition¶
When a workbook intentionally carries the same record across multiple
tabs (a multi-tab report whose tabs each repeat a shared set of
records), the validator's default duplicate_row check fires N
times per record.
locate.duplicate_policy: ignore suppresses that check for the
entity so the cross-tab repetition lands as data rather than as a
chorus of errors:
Catching structural-noise extracts¶
When the wrong template is applied to a file (header_row picked the
wrong row, the file is a paper-form layout that doesn't really fit
the template), extraction still emits records — most of them
mostly-blank. locate.min_data_density surfaces this as a single
low_data_density warning rather than dozens of
missing_required errors:
locate:
tab: Sheet1
orientation: flat
header_row: 0
min_data_density: 0.5 # warn if <50% of cells across rows are populated
Classifying files before extraction¶
crease.classify(path, template) returns a ClassifyVerdict so a
pipeline can short-circuit the full extract / validate / project flow
when a file plainly doesn't match the template (wrong layout, missing
tab, a paper-form file mistaken for a table):
verdict = crease.classify("incoming.xlsx", template)
if verdict.fit == "not_templatable" or verdict.confidence < 0.3:
route_to_human_review(...)
else:
result = crease.extract("incoming.xlsx", template)
The implementation runs extract and inspects the structural error
codes. classify itself never raises — file-open failures land as
fit="not_templatable", confidence=0.0 rather than as exceptions.
File-open failures surface in the report¶
extract no longer raises SourceFileError for missing / corrupt /
encrypted / wrong-format files. The failure lands in
result.errors() as a structural unreadable_source error and
report.has_structural is True. The SourceFileError class is
still exported so existing except crease.SourceFileError: clauses
remain importable, but no API path now raises it.
Templates that pin the read backend¶
Crease reads spreadsheets through two interchangeable backends — calamine
(the default; reads .xlsx, .xls, .xlsb, .ods) and openpyxl (.xlsx
only, but exposes cell metadata calamine does not).
One template feature forces openpyxl: locate.skip_hidden_rows: true.
Calamine doesn't surface the row-hidden flag, so a template that needs to
drop hidden rows is auto-dispatched to openpyxl. The side effect is that
such templates can't read .xls / .xlsb / .ods — those formats live
on the calamine path only.
entities:
- name: order
locate:
tab: Orders
orientation: flat
skip_hidden_rows: true # → openpyxl backend; .xlsx only
If you'd rather have multi-format support and accept that hidden-row detection won't fire, override at call time: