Excel Importer¶

Verified against the code · 19d408be · 2026-08-25

Every canonical operation, signature, status value, error path, and behavioral claim in the doc page matches backend/api/excel_importer.py exactly as it exists on disk (parse_excel, normalize_paths, parse_relation_rows/resolve_relation_rows, parse_property_rows/resolve_property_rows, parse_product_node_rows, fuse_anchor_first_segment, relation_import_idem_key, _load_workbook_guarded all verified line-for-line, including exact error message strings and status-value sets).

Layer: api

Overview¶

The Excel Importer engine (backend/api/excel_importer.py) is the pure-function parsing and resolution layer for all Excel-based batch import workflows in EIDOS Explorer. It implements the AddFromExcel contract (§4.13, v3.6.0).

"Pure-function" is the defining constraint: this module performs no I/O, makes no writes, and holds no state. Every function takes explicit arguments and returns a value. The actual writes — creating nodes, edges, and properties in the graph — are handled by the batch executor in server.py. The engine's job is to convert bytes into structured, validated, resolved command descriptions that the executor can act on.

The engine covers four import surfaces:

Node import — parse_excel / normalize_paths: converts an .xlsx sheet of node paths and descriptions into a sorted, deduplicated sequence of path descriptors ready for AddNodeCommand execution.
Relation import — parse_relation_rows / resolve_relation_rows: converts an .xlsx sheet of (source, target, predicate) triples into fully-resolved relation previews annotated with status, UUIDs, and resolution mode.
Property import — parse_property_rows / resolve_property_rows: converts an .xlsx sheet of glob patterns and property name/value pairs into per-node property assignments matched against the effective graph.
Product node import — parse_product_node_rows / fuse_anchor_first_segment: a specialised node parser that handles ISO 81346 domain sigils, NodeType taxonomy, and anchor-relative template import.

All four surfaces share the same file-loading guard, the same SHA-256 hashing convention, and the same sheet-selection fallback behaviour.

parse_excel — Full Signature and Semantics¶

def parse_excel(
    file_bytes: bytes,
    sheet_name: str | None = None,
) -> tuple[list[dict], str]:

Parameters¶

Param	Type	Description
`file_bytes`	`bytes`	Raw bytes of the .xlsx file, as received from the HTTP request body or multipart upload.
`sheet_name`	`str \\| None`	Name of the worksheet to read. `None` uses the workbook's active sheet. An unrecognised name also falls back to the active sheet — a stale client selection never causes a hard failure.

Return value¶

A two-tuple (rows, file_hash):

rows — a list[dict], each element {"path": str, "description": str}. Rows whose Path cell is empty or whitespace-only are silently dropped. The list preserves spreadsheet order.
file_hash — a hex-encoded SHA-256 digest of the raw file_bytes, computed before any parsing. This hash is stable: the same bytes always yield the same hash regardless of sheet selection or parse outcome.

The parsing pipeline¶

Step 1 — Hash. hashlib.sha256(file_bytes).hexdigest() is called on the raw bytes before the workbook is opened. The hash captures the exact file the user uploaded, not any derived representation.

Step 2 — Load. _load_workbook_guarded wraps openpyxl.load_workbook with read_only=True, data_only=True. read_only=True avoids loading the entire workbook object model into memory; data_only=True discards formula expressions and returns computed cell values.

Step 3 — Sheet selection. If sheet_name is provided and exists in wb.sheetnames, that sheet is used. Otherwise wb.active is used. This fallback is intentional: a frontend that remembered the sheet name from a previous upload continues to work even if the sheet has been renamed, rather than returning an error.

Step 4 — Header detection. Row 0 of the sheet is treated as the header row. Each cell is lowercased and stripped. The engine looks for a column whose lowercased header is "path". If not found, a ValueError is raised: "Excel file must contain a 'Path' column". A "description" column is optional; if absent, all descriptions default to "".

Step 5 — Row extraction. For each subsequent row, the engine reads path_col and desc_col. Rows where the path cell is empty, None, or whitespace-only are skipped without error. Valid rows are appended as {"path": str(path_val).strip(), "description": str(desc_val).strip()}.

Column mapping¶

Spreadsheet column	Key in row dict	Required	Notes
`Path` (case-insensitive)	`"path"`	Yes	Raises `ValueError` if the column is absent
`Description` (case-insensitive)	`"description"`	No	Defaults to `""` if the column is absent or the cell is empty

What it does not do¶

parse_excel does not expand ancestor paths, deduplicate, or sort. That is the responsibility of normalize_paths. parse_excel also does not evaluate commands or touch the graph.

normalize_paths — Full Signature and Semantics¶

def normalize_paths(rows: list[dict]) -> list[dict]:

Parameters¶

Param	Type	Description
`rows`	`list[dict]`	Output of `parse_excel` — each element must have at least `"path"` and optionally `"description"`.

What "path normalization" means¶

In EIDOS Explorer the graph is hierarchical: a node at path UCA10.QAB01.K02 can only exist if UCA10.QAB01 and UCA10 already exist. An Excel file that lists only leaf paths would produce broken imports unless every intermediate ancestor is also created first.

normalize_paths solves this by expanding every leaf path into all of its ancestor paths, then deduplicating and sorting the expanded set so that parents always appear before children.

How the expansion works¶

For each row in rows:

The "path" value is split on "." and whitespace-stripped per segment.
Every prefix of the segment list generates an ancestor path. For ["UCA10", "QAB01", "K02"], this yields "UCA10", "UCA10.QAB01", and "UCA10.QAB01.K02".
Each generated path is lowercased for deduplication purposes, but the original casing is preserved in the output.
Intermediate ancestor paths are created with description: "" unless a row in the input explicitly provides a path equal to that ancestor (in which case the leaf rule below applies).
The last row in the input that references a given path wins its description. This means that if two input rows have the same path, the later one's description is used.

Sort order¶

The output list is sorted by the key (path.count("."), path) — that is, by depth first (shallower paths sort earlier), then lexicographically within the same depth. This guarantees that every parent node is created before any of its children, regardless of spreadsheet order.

Return value¶

A list[dict], each element:

{
    "path": str,        # original-case path string, e.g. "UCA10.QAB01"
    "path_lower": str,  # lowercase path, e.g. "uca10.qab01"
    "description": str, # empty string for synthetic ancestors
}

Example¶

Input rows:

[{"path": "UCA10.QAB01", "description": "Sub-assembly 1"},
 {"path": "UCA10.QAB02", "description": "Sub-assembly 2"}]

Output after normalize_paths:

[{"path": "UCA10",         "path_lower": "uca10",         "description": ""},
 {"path": "UCA10.QAB01",   "path_lower": "uca10.qab01",   "description": "Sub-assembly 1"},
 {"path": "UCA10.QAB02",   "path_lower": "uca10.qab02",   "description": "Sub-assembly 2"}]

UCA10 is created once, not twice, even though both input rows reference it as an ancestor.

parse_relation_rows and resolve_relation_rows¶

parse_relation_rows¶

def parse_relation_rows(
    file_bytes: bytes,
    sheet_name: str | None = None,
) -> tuple[list[dict], str, list[dict]]:

Parses an .xlsx file whose rows describe graph relations. The workbook is opened with data_only=False — this is intentional and critical: paths that begin with = (which is valid in ISO 81346 path syntax) must be read as string literals, not evaluated as Excel formulas. Opening with data_only=True would silently corrupt these paths.

Required columns (case-insensitive): source_path, target_path, relation_type. Missing any of these raises ValueError. Optional columns: inherit_source, is_inherited.

Per-row validation: each of the three required fields must be non-empty. Rows with an empty required field are added to parse_errors with a message identifying the row index and the field; they are not included in rows.

Return shape: (rows, file_hash, parse_errors)

rows — list[dict], each: {row_idx, source_path, target_path, relation_type, inherit_source: bool, is_inherited: bool}
file_hash — SHA-256 of raw bytes
parse_errors — list[dict], each: {row_idx, message}

resolve_relation_rows¶

def resolve_relation_rows(
    rows: list[dict],
    eff: dict,
    scope_node_id: str | None = None,
    base_node_id: str | None = None,
) -> list[dict]:

Takes parsed rows and the eff (effective graph snapshot) and resolves each row to a preview dict with status, UUIDs, and resolution metadata.

eff must contain: nodes (uid → node dict), trees (list of tree root dicts), ref_by_uid (uid → path string), children_of (uid → list of child uids).

Status values emitted per row:

Status	Meaning
`OK`	Both source and target resolved to exactly one local node, predicate is valid.
`SOURCE_MISS`	Source path resolved to zero nodes (or was EXTERNAL, which is disallowed for sources).
`SOURCE_AMBIGUOUS`	Source path matched more than one node.
`TARGET_MISS`	Target path resolved to zero nodes AND the predicate is a Hierarchy predicate (hard block).
`UNRESOLVED`	Target path resolved to zero nodes AND the predicate is Associative — importable as a deferred LOCAL reference.
`TARGET_AMBIGUOUS`	Target path matched more than one node.
`EXTERNAL`	Target path carries a sigil token not present in any loaded local tree — it is a cross-domain reference.
`INVALID_PREDICATE`	`relation_type` is not a known predicate id from the relation catalog.
`OUT_OF_SCOPE`	Source node is not a descendant of `scope_node_id`.
`INHERITED_SKIP`	Row has `is_inherited: True` — inherited relations are computed dynamically and must not be stored.
`DUPLICATE`	Same (srcUuid, tgtUuid, relationType) tuple already appeared earlier in the file.

Anchor-relative paths: when base_node_id is provided, a path without a leading sigil is treated as anchor-relative. The first segment is treated as a placeholder for the anchor node and is stripped; the remaining segments are joined onto the base node's ref path. A path with a leading sigil is always treated as absolute. Each result carries sourceMode / targetMode (REL | ABS | EXT) and resolvedSourcePath / resolvedTargetPath for display in the preview UI.

parse_property_rows and resolve_property_rows¶

parse_property_rows¶

def parse_property_rows(
    file_bytes: bytes,
    sheet_name: str | None = None,
) -> tuple[list[dict], str, list[dict]]:

Parses an .xlsx file where each row assigns a property to nodes matching a path glob pattern.

Required columns: path, property_name. Optional: value, unit, group, inherit.

The path column holds a dot-segmented glob pattern (e.g. UCA10.QA*). * and ? do not cross dots. [...] character classes are accepted. ** is explicitly forbidden — the error message redirects authors to use inherit: true instead. Each pattern is compiled to a full-match regex via _pattern_to_regex at parse time; rows with invalid patterns (containing **) are added to parse_errors.

The inherit column is truthy if the cell value (lowercased) is in {"true", "1", "yes"}.

Return shape: (rows, file_hash, parse_errors)

rows — list[dict], each: {row_idx, pattern, property_name, value, unit, group, inherit: bool}
file_hash — SHA-256 of raw bytes
parse_errors — list[dict], each: {row_idx, message}

resolve_property_rows¶

def resolve_property_rows(rows: list[dict], eff: dict) -> list[dict]:

Expands each parsed pattern row against the effective graph's ref_by_uid map. For each row, every uid whose ref path fully matches the compiled regex is collected into matched_nodes. Results are sorted by node label.

Return shape: list[dict], each:

{
    "pattern": str, "property_name": str, "value": str, "unit": str,
    "group": str, "inherit": bool,
    "matched_nodes": [{"uid": str, "label": str}, ...],
    "match_count": int,
    "status": "ok" | "no_match",
}

There are only two status values. A row with zero matches gets "no_match"; a row with one or more matches gets "ok". Unlike relation resolution, there is no AMBIGUOUS status — a property assignment to multiple matching nodes is the expected and intended behaviour of pattern-based import.

Idempotency¶

relation_import_idem_key¶

def relation_import_idem_key(
    file_hash: str,
    base_node_id: str | None,
    row_idx,
) -> str:

Generates the idempotency key stored alongside each imported relation. The key has the form:

excel-rel-{file_hash[:16]}-{anchor}-row{row_idx}

where anchor is base_node_id if provided, otherwise the string "root".

Why the anchor is included: A relation-template file is frequently reused under multiple parent nodes. If the key were keyed on (file_hash, row_idx) alone, importing the same template under a second parent (e.g. HG21 after HG20) would produce identical keys for every row, causing the executor to treat every relation as already existing and skip the entire import. Including base_node_id in the key ensures that the same (file, row) pair imported under different anchors is treated as a distinct operation, while a genuine re-import of the same (file, anchor, row) triple remains idempotent. This distinction is required by CCR 16.

The executor in server.py calls this function when building its write commands; it checks whether the key already exists in the graph's stored idem-key set before attempting a write.

Error Handling¶

_load_workbook_guarded¶

def _load_workbook_guarded(file_bytes: bytes, **kwargs):

This private function is the single choke-point for all workbook loading in the engine. It wraps openpyxl.load_workbook(io.BytesIO(file_bytes), **kwargs) and catches two exceptions that openpyxl raises on non-.xlsx payloads:

openpyxl.utils.exceptions.InvalidFileException — raised when the byte stream is not recognised as an OOXML file at all (e.g. a PDF, CSV, or plain text file).
zipfile.BadZipFile — raised when the byte stream looks like a ZIP archive (which .xlsx is) but is corrupt or truncated.

Without this guard, both exceptions would propagate past the import endpoints, which catch only ValueError, and surface to the client as a generic HTTP 500. With the guard, both are re-raised as ValueError("File is not a valid .xlsx spreadsheet."), which the endpoints convert to an HTTP 400 PARSE_ERROR response (Contract 05 R5/R13/R31). This is tracked as BL-IE-035 / issue #152.

Validation that surfaces to the caller¶

Function	Condition	Raised as
`_load_workbook_guarded`	Non-OOXML or corrupt ZIP bytes	`ValueError: "File is not a valid .xlsx spreadsheet."`
`parse_excel`	No `Path` column in header row	`ValueError: "Excel file must contain a 'Path' column"`
`parse_relation_rows`	Any of `source_path`, `target_path`, `relation_type` columns absent	`ValueError` listing the missing column names
`parse_product_node_rows`	Any required column absent (`reference`, `description`, and `nodetype` when `require_node_type=True`)	`ValueError` listing the missing column names
`parse_property_rows`	`path` or `property_name` column absent	`ValueError` listing the missing column names
`parse_property_rows`	A `path` cell contains `**`	Row added to `parse_errors`; not raised
`_pattern_to_regex`	`**` in pattern string	`ValueError` (caught by parse and resolve functions)

Row-level validation errors (empty required cells, unknown NodeType values, invalid patterns) are collected into parse_errors lists and returned to the caller rather than raised. The caller — typically an endpoint in server.py — decides whether to abort the import or proceed with only the valid rows.

Integration¶

How the import-pipeline workflow uses these functions¶

The import pipeline is a two-phase operation: a preview phase and a commit phase, both driven by endpoints in server.py.

Preview phase:

The endpoint receives file_bytes from the HTTP request.
It calls parse_excel(file_bytes, sheet_name) (or the appropriate parse function for the import type) to obtain (rows, file_hash).
For node import, it calls normalize_paths(rows) to obtain the full sorted command sequence.
For relation import, it calls resolve_relation_rows(rows, eff, scope_node_id, base_node_id) against the current effective graph to compute per-row status.
The preview result — including file_hash — is returned to the client. No writes occur.

Commit phase:

The client sends a commit request that includes the original file_bytes and the file_hash computed during preview.
The endpoint re-derives the command sequence by calling the same parse and normalize functions on the same bytes. This guarantees that the command sequence at commit time is identical to the command sequence at preview time, even if the graph changed between the two phases (CCR 16).
The executor walks the derived sequence, checks each command's idempotency key, and writes only those commands that have not already been executed.

What the caller must pass¶

For node import: file_bytes: bytes and optionally sheet_name: str. No graph state is needed — parse_excel and normalize_paths are entirely stateless.

For relation import: file_bytes, optionally sheet_name, plus the eff dict (effective graph snapshot), scope_node_id (optional, to restrict to a subtree), and base_node_id (optional, to enable anchor-relative path resolution).

For property import: file_bytes, optionally sheet_name, plus eff for resolve_property_rows.

What the caller receives¶

All parse functions return a three-tuple (rows, file_hash, parse_errors) (the simpler parse_excel returns (rows, file_hash) without a parse_errors list, since its only row-level rule is silent skipping of empty path cells).

file_hash must be stored by the caller and passed back in the commit request. It is the stable identifier that links a preview to its commit, and it is the first component of every idempotency key generated by relation_import_idem_key.

All resolve functions return a list of preview dicts. These dicts are passed directly to the frontend for display; they are also the input to the commit executor, which reads the status, UUID, and metadata fields to decide what to write.