Optima Report Parser

This project parses a Comarch ERP Optima accounting report (Excel export) and converts it to JSON.

The output JSON contains:

• a list of accounting postings (journal entry blocks),
• optional report totals from the footer section.

What It Does

• Loads a legacy .xls report file.
• Converts it to an openpyxl workbook in memory.
• Parses Optima report blocks (header + posting lines + totals).
• Exports structured data to result.json.

In short: it is an Optima report to JSON parser for a bookkeeping postings list.

Project Structure

• main.py - entry point, runs conversion and parsing, writes result.json.
• xls_to_xlsx.py - converts .xls files to openpyxl workbook objects.
• optima_report.py - parsing logic for document blocks and footer totals.
• xlregex.py - lightweight row/block pattern-matching helpers for Excel sheets.

Requirements

• Python 3.13+
• Poetry (recommended)

Dependencies:

• openpyxl
• xlrd

Installation

poetry install

Usage

1. Put your Optima export file in the project directory (default name in current script: excel.xls).
2. Run:

poetry run python main.py

3. Parsed output will be saved to:

result.json

Output Format (JSON)

Top-level output includes:

• blocks: list of accounting postings (journal entries),
• footer aggregate fields when present (turnover/balance values).

Each block typically contains:

• entry metadata (entry_id, journal_number, document_number, operator, etc.),
• items: detailed debit/credit posting lines,
• debit_total and credit_total.

Example (shortened):

{
  "blocks": [
    {
      "entry_id": "1",
      "journal_number": "ZAKUP/2024/02/1",
      "items": [
        {
          "line_id": "1/1",
          "debit_account": "503",
          "debit_amount": 11932.49,
          "credit_account": "",
          "credit_amount": 0.0
        }
      ],
      "debit_total": 11932.49,
      "credit_total": 11932.49
    }
  ]
}

Customization

If your report sheet name or file name is different, update main.py:

• input file passed to cvt_xls_to_xlsx(...),
• worksheet name (currently "Arkusz2"),
• output file path if needed.

Notes

• Parser patterns are tailored to the Optima report layout used in this project.
• If your export format differs, adjust row/block patterns in optima_report.py.