pain001-loader-xlsx 0.0.53

Read flat-record payment data from Excel .xlsx files for pain001.

pain001-loader-xlsx

Excel (.xlsx / .xlsm) loader plugin for the pain001 ISO 20022 payment library.

---

Contents

Getting started

• What is pain001-loader-xlsx? — the problem it solves
• Install — PyPI, virtualenv
• Quick start — one command from Excel to validated XML

Library reference

• How it works — the plugin contract, in one diagram
• Layout — sheet selection, headers, the IBAN safety guard
• Using the loader from Python — bypass pain001 entirely
• The pain001 suite — core lib, MCP server, LSP server, this loader

Operational

• When not to use pain001-loader-xlsx — honest boundaries
• Development — gates, make targets
• Security — defensive posture
• Documentation — examples, guides
• Contributing — how to get changes in
• License — Apache-2.0

---

What is pain001-loader-xlsx?

pain001-loader-xlsx is a third-party loader plugin for the pain001 ISO 20022 payment library that teaches it to read payment data directly from Excel .xlsx / .xlsm files — no "Save As CSV" step.

Drop-in: install both packages and .xlsx files dispatch automatically. Nothing else changes about how you use pain001.

This package exists because openpyxl carries a non-trivial transitive dependency tree that operators who only need CSV / SQLite / JSON would rightly object to as part of the core install. It is also the canonical worked example of pain001's v0.0.54+ plugin substrate — the same AbstractLoader Protocol any third-party loader uses.

Concern	How the loader handles it
Sheet selection	First sheet wins. Consolidate cross-sheet batches before invocation.
Excel formulas	Cached value is read (data_only=True) — what you see in Excel is what pain001 gets.
IBAN columns	Refused when the cell type is General (Excel silently strips leading zeros from numeric-looking strings). The error tells the user to re-type the column as Text and re-export.
Date cells	Cached value is passed through; ISO 8601 strings work, Excel datetime objects depend on the underlying spreadsheet.
Blank header cells	Stay in the dict under key ""; downstream consumers can ignore or strip.
Streaming	load_streaming(path, chunk_size) honours pain001's --streaming mode.
Discovery	Registered via the standard pain001.loaders entry-point group; pain001 picks it up at process start.
Cross-platform	Pure Python (openpyxl); works wherever pain001 works.

---

Install

Channel	Command	Notes
PyPI	pip install pain001 pain001-loader-xlsx	Pulls in openpyxl >= 3.1 + a recent pain001
Source	git clone https://github.com/sebastienrousseau/pain001-loader-xlsx && cd pain001-loader-xlsx && pip install -e ".[dev]"	For development

Requires Python 3.10 or later. Works on macOS, Linux, and Windows.

pain001 version requirement. The plugin substrate (pain001.plugins) that powers auto-discovery is part of pain001 >= 0.0.54. The package metadata declares this dependency explicitly; pip install pain001-loader-xlsx will pull in a compatible pain001 automatically.

Using an isolated virtual environment (recommended)

python -m venv venv
source venv/bin/activate        # macOS/Linux
venv\Scripts\activate           # Windows
python -m pip install -U pain001 pain001-loader-xlsx

---

Quick start

Install both packages and feed pain001 an .xlsx file:

pip install pain001 pain001-loader-xlsx

pain001 -t pain.001.001.03 -d payments.xlsx -o out.xml
# -> writes a validated pain.001.001.03 XML alongside out.xml

Confirm pain001 sees the loader:

pain001 plugins list --kind loader --json | python -m json.tool
# [
#   { "kind": "loader", "name": "xlsx",
#     "source": "pain001-loader-xlsx==0.0.53", ... },
#   ...
# ]

From Python:

from pain001 import process_files

# pain001's universal loader dispatches .xlsx through this plugin.
output_path = process_files(
    xml_message_type="pain001.001.001.03",
    xml_template_file_path="template.xml",
    xsd_schema_file_path="schema.xsd",
    data_file_path="payments.xlsx",
)
print(output_path)  # -> "pain.001.001.03.xml" — validated and on disk

---

How it works

+--------------------------+        +--------------------------+
|  pain001 CLI / REST API  |        |  pain001-loader-xlsx     |
|                          |        |                          |
|  load_payment_data(path) |  -->   |  XlsxLoader.load(path)   |
|                          |        |  -> LoaderResult         |
+----------+---------------+        +----------+---------------+
           |                                   |
           | extension dispatch (.xlsx)         |
           v                                   v
    +------+-------+                    +------+-------+
    |  pain001     |                    |  openpyxl    |
    |  registry    |                    |  read_only   |
    +--------------+                    +--------------+

pain001 v0.0.54 ships a formal plugin contract; this package exposes one Python class that satisfies it. Wired into pain001 via a single line in this package's pyproject.toml:

[project.entry-points."pain001.loaders"]
xlsx = "pain001_loader_xlsx.loader:XlsxLoader"

That is all the integration there is. pain001 discovers the entry point at process start via importlib.metadata.entry_points and dispatches by extension. There is no global state, no central registry to update, nothing to subclass.

---

Layout

The first sheet of the workbook is read. Row 1 is the header (column names become dict keys); rows 2..N are the data records. Cells are read with openpyxl's data_only=True so formulas resolve to their cached last-saved value — what the user sees in Excel is what pain001 gets.

The IBAN guard, explained

Excel's "General" cell format silently coerces a numeric-looking string like 0023012345... into the integer 23012345..., dropping the leading zeros. This is a known data-corruption mode in SAP / Oracle / Workday exports. To protect against it the loader refuses any row whose debtor_account_IBAN / creditor_account_IBAN / charge_account_IBAN cell is typed as a number, and tells the user to re-type the column as Text:

workbook 'payments.xlsx' column 'debtor_account_IBAN' contains
a numeric value (89370400440532013000) where an IBAN string is
expected. Excel's 'General' cell format silently strips leading
zeros from IBANs; re-type the column as 'Text' (in Excel: select
the column, Format Cells -> Number -> Text) and re-export.

Caught early, the warning saves the user from wiring an IBAN with a missing digit to a bank.

---

Using the loader from Python

For Lambdas, ETL pipelines, or just inspecting an Excel file's records before generation, you can use XlsxLoader directly without going through pain001's dispatch:

from pain001_loader_xlsx import XlsxLoader

loader = XlsxLoader()
result = loader.load("payments.xlsx")

print(result.source_hint)        # -> "payments.xlsx"
print(len(result.rows))          # -> 42
print(result.rows[0]["id"])      # -> "MSG-0001"

Streaming variant for batches that don't fit in memory:

for chunk in loader.load_streaming("big-payments.xlsx", chunk_size=1000):
    process(chunk.rows)

The runnable version of this snippet (and a couple of others) lives in examples/.

---

The pain001 suite

pain001-loader-xlsx is part of a set of independently installable packages built around the pain001 library — pick whichever ones your stack needs:

Package	Role
pain001	Core library + CLI + FastAPI REST API
pain001-mcp	Model Context Protocol server (for AI agents)
pain001-lsp	Language Server Protocol server (for editors)
pain001-loader-xlsx	Excel loader plugin (this package)

flowchart LR
    A["payments.xlsx"] -->|extension dispatch| B["pain001-loader-xlsx"]
    B -->|LoaderResult| C["pain001"]
    C -->|render + XSD validate| D["ISO 20022 pain.001 XML"]

---

When not to use pain001-loader-xlsx

• You can export CSV cleanly. A .csv round-trip skips an entire transitive dependency tree (openpyxl + its handful of deps). pain001's built-in CSV loader is preferred when you have the choice.
• You need multi-sheet support. The first sheet wins; cross-sheet payment batches need to be consolidated first.
• You need .xls (legacy binary format). Out of scope. Convert to .xlsx first, or use a different loader.
• Your data isn't payment-record-shaped. This loader is a thin pain001 input adapter, not a general-purpose Excel reader.

---

Development

pain001-loader-xlsx uses standard Python tooling — no Poetry, just pip + pyproject.toml.

git clone https://github.com/sebastienrousseau/pain001-loader-xlsx.git
cd pain001-loader-xlsx
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

Quality gates (kept in lockstep with CI):

Target	What it runs
pytest	Full test suite
pytest --cov=pain001_loader_xlsx --cov-branch --cov-fail-under=100	100% line + branch coverage gate
interrogate -c pyproject.toml pain001_loader_xlsx	100% docstring coverage gate
ruff check pain001_loader_xlsx tests	Lint
ruff format --check pain001_loader_xlsx tests	Format
mypy pain001_loader_xlsx	Type check

Current state (v0.0.53): 12 tests passing, 100% line + branch coverage, ruff + mypy clean, interrogate 100% docstring coverage.

---

Security

• No filesystem writes. The loader reads from an Excel file path and yields plain dicts; it does not create, modify, or delete files.
• No code execution. openpyxl's read_only=True mode does not evaluate macros (Excel VBA is not executed). data_only=True returns the cached last-saved value of formulas — no formula engine runs.
• IBAN safety: the loader refuses any row whose IBAN cells are numeric (see Layout), avoiding the "Excel silently dropped a leading zero" data-corruption mode.
• Dependencies are pinned via pyproject.toml (openpyxl >= 3.1, < 4) and audited by GitHub's Dependabot.

To report a vulnerability, please use GitHub private vulnerability reporting rather than a public issue.

---

Documentation

• Runnable examples: examples/
• Release history: CHANGELOG.md
• pain001 plugin contract: docs/plugins.md upstream
• openpyxl docs: openpyxl.readthedocs.io

---

Contributing

Contributions are welcome — see the contributing guide (or the upstream pain001 contributing guide if a per-repo one has not landed yet). Thanks to all the contributors who have helped build pain001-loader-xlsx.

---

License

Licensed under the Apache License, Version 2.0. Built on openpyxl and the pain001 plugin contract.

Any contribution submitted for inclusion shall be licensed as above, without additional terms.

---

pain001.com · PyPI · GitHub