mitsubishi-label-converter-py/README.md

# mitsubishi-label-converter-py

A Python CLI tool that converts Mitsubishi GX Works3 global label XML exports
(`Global.xml`, PLCopen TC6 format, typically UTF-16 LE encoded) **to Excel**
and **back to XML** with a lossless round-trip.

Each `<globalVars>` section group in the source file becomes its own worksheet:
sections sharing the same name (e.g. a plain and a retain ``Global`` list)
are merged into one sheet, with the per-variable retain flag in the
``Retain`` column. The reverse conversion splits the rows back into the
original sections. Every `<variable>` becomes one row with:

| Column    | Content                                                                 |
|-----------|-------------------------------------------------------------------------|
| Name      | The variable's `name` attribute                                          |
| Address   | The variable's `address` attribute (empty when unassigned)               |
| Type      | The declared type (see below)                                            |
| Comment   | The variable comment from `variableComments` addData (if present)        |
| Line      | Original source line number from `variableLineNumber` addData            |
| Retain    | `TRUE` when the variable belongs to a `retain="true"` globalVars section |

**Structured variables** (derived types like `ST_Din_STA`) are expanded: each
member with a device address gets its own row directly under the variable,
using a dotted name (`PLC_FEL_STA.Value -> D0.0`). The addresses come from
`variableStructDeviceAssignment` addData.

**Member types** — the Global XML export carries only member names and
addresses; member *types* live in a separate SDT export. Pass it via `--sdt`
to populate the Type column of member rows (`BOOL`, `WORD`, `STRING[16]`,
`ARRAY[1..4] OF INT`, nested struct names, ...). Without the flag, member
Type cells stay empty.

A **`Meta`** worksheet records document metadata (fileHeader, contentHeader,
PLC type) plus the sheet-to-section mapping (names and `varListProperties`
timestamps) so the reverse conversion is lossless. Retain information lives
in the `Retain` column, not in Meta.

## Type rendering

- Primitive types (`<INT/>`, `<BOOL/>`, `<REAL/>`, `<WORD/>`, ...) are written as-is.
- `<string length="12"/>` is written as `STRING[12]`.
- Structured/derived types (`<derived name="FB_Din"/>`) are written using the
  derived name, e.g. `FB_Din`.

The reverse conversion recognizes primitives from the standard IEC set;
everything else is emitted as a `<derived>` reference again.

## Validation report

The `report` subcommand runs consistency checks on an XML export or an Excel
workbook and prints the findings grouped by severity:

| Severity | Check | Code |
|----------|-------|------|
| error   | duplicate variable names | `NAME_DUPLICATE` |
| error   | identical device address used by several variables | `ADDR_DUPLICATE` |
| warning | bit address inside another variable's word range | `ADDR_BIT_IN_WORD` |
| warning | word ranges of two variables overlap (types sized in 16-bit registers; `STRING[n]` occupies `(n+2)/2` registers) | `ADDR_OVERLAP` |
| warning | address in an unrecognized format | `ADDR_FORMAT` |
| warning | struct member not defined in the SDT struct | `MEMBER_UNKNOWN` |
| info    | struct members without a device address | `MEMBER_UNASSIGNED` |
| info    | structured variable without any device assignment | `STRUCT_UNASSIGNED` |
| info    | derived type missing from the SDT file (aggregated per type) | `TYPE_UNKNOWN` |

Members of the same structured variable may legitimately share word
registers, so only cross-variable collisions are reported. The command
exits with code 1 when errors are found (0 for warnings/info only), so it
can gate CI pipelines. Address parsing handles word-level (`D450`),
bit-level (`D6005.0`), and hexadecimal bit suffixes (`D201.A` = bit 10).

## Installation

```bash
# from the project root
pip install -e .[dev]
```

## Usage

```bash
# XML -> Excel: output is written next to the source file (Global.xlsx)
mitsubishi-label-converter to-xlsx assets/Global.xml

# with member types from an SDT (structured data type) export
mitsubishi-label-converter to-xlsx assets/Global.xml --sdt assets/SDT.xml

# explicit output path
mitsubishi-label-converter to-xlsx assets/Global.xml -o labels.xlsx

# Excel -> XML: regenerate a GX Works3-compatible XML file
mitsubishi-label-converter to-xml assets/Global.xlsx -o output/Global.xml

# validate labels (XML or Excel input); exit code 1 on errors (CI-friendly)
mitsubishi-label-converter report assets/Global.xml --sdt assets/SDT.xml

# save the report to a file instead of stdout
mitsubishi-label-converter report assets/Global.xlsx -o output/validation.txt

# overwrite existing outputs
mitsubishi-label-converter to-xlsx assets/Global.xml --force
```

The round-trip is lossless: parsing the regenerated XML yields exactly the
same sections, variables, members, comments, line numbers, retain flags,
and metadata as the original file. The `to-xml` conversion regroups the
rows of a merged sheet by their `Retain` flag, restoring the original
section split (plain first, retain second). Member types from the SDT file
are Excel-only decoration and are intentionally not written to the XML (the
XML format stores member types in its `dataTypes` section, not in device
assignments).

## Development

```bash
pip install -e .[dev]
pytest
```

## Building a self-executing exe

The CLI can be packaged as a standalone Windows executable with PyInstaller.

```bash
pip install -e .[dev]      # includes pyinstaller

# one-folder build (default): fast start-up, distribute the whole folder
.\build.ps1

# single-file build: one .exe, slower start-up (self-extracting)
.\build.ps1 -Onefile

# rebuild from scratch and smoke-test the exe against assets/
.\build.ps1 -Clean -Test

# explicit interpreter if several Pythons are installed
.\build.ps1 -Python C:\Path\To\python.exe
```

Results land in `dist/`:

- **one-folder** (default): `dist/mitsubishi-label-converter/mitsubishi-label-converter.exe`
  plus supporting DLLs — distribute the whole folder; starts fast.
- **onefile**: `dist/mitsubishi-label-converter.exe` — a single 8.8 MB file to
  copy anywhere; a little slower to start.

Notes:

- The script prefers the project `.venv`, then `python`/`py` on PATH; the
  Microsoft Store `python` alias is detected and skipped automatically.
- `run.py` at the project root is the packaging entry point (PyInstaller cannot
  execute `cli.py` directly because of its relative imports).
- Antivirus software may flag unsigned PyInstaller exes; sign the exe or add
  an exclusion if this matters in your environment.
- Build artifacts (`build/`, `dist/`, `*.spec`) are git-ignored.

## TODO / Roadmap

Done:

- [x] **Self-executing exe** — `build.ps1` wraps PyInstaller with
  one-folder/onefile modes, clean rebuild, built-in smoke test, and robust
  Python resolution; verified all three subcommands and the lossless
  round-trip through the packaged exe.

- [x] **Expose struct members with addresses in Excel** — dotted member rows
  inline under each structured variable (189 structured variables -> 1,220
  member rows in the sample file).
- [x] **Member types from SDT export** — `--sdt` flag populates the Type
  column of member rows from a structured data type XML export; without it
  the workbook is unchanged.
- [x] **Merged sheets + Retain column** — sections sharing a name merge into
  one worksheet; the per-variable `Retain` column marks retain variables,
  and `to-xml` regroups rows by flag to restore the original sections.
- [x] **Validation report** — `report` subcommand checks duplicate
  names/addresses, address overlaps (bit-in-word, word ranges), struct
  member consistency, and unassigned structs; works on XML and Excel
  input; CI-friendly exit codes.
- [x] **Excel -> XML round-trip conversion** — `to-xml` subcommand;
  `Meta` sheet carries document metadata and sheet mapping; verified
  lossless against the real 290-variable file.
- [x] **CLI subcommands** — `to-xlsx` / `to-xml` with `-o/--output` and
  `--force` flags.

Planned:

1. **`--all-members`: expose SDT members without device addresses (requested)**
   GX Works3 only exports struct members that carry a device address; a
   structured variable without any assignment (e.g. `GP01_RUNTIME_PAR` of
   type `ST_Runtime_PAR`) has no member rows at all — its internal members
   (`StartsTot`, `RunTimeTot`, ...) exist only in the SDT file. Plan:
   - `expand_missing_members()` in `sdt_parser.py`: append every SDT-defined
     member missing from the device assignment as an address-less row
     (Type populated, Retain inherited from the parent variable).
   - `--all-members` flag on `to-xlsx` (requires `--sdt`).
   - `xml_writer` must skip address-less `<member>` entries so `to-xml`
     stays lossless (the original XML has no such members).
   - Optional follow-up: recursive expansion for nested struct members
     (`Parent.Sub.uHour`, e.g. `stError_Time -> M+FX5UCPU_Error_Time`).
   - Verification: fabricated fixture (`tests/test_all_members.py`) with
     (a) a partially assigned struct, (b) a struct variable with no
     assignment at all, (c) both in the retain section.

2. **CLI conveniences**
   - `--list` flag: print the parsed label tables to the terminal.
   - `--sheet-per-type` flag: split variables into one worksheet per type.
   - `--filter <glob>` flag: convert only variables matching a name pattern.
   - `--round-trip` flag: convert back and forth and verify losslessness
     automatically.

3. **Multiple input files** — accept several XML files in one invocation,
   each producing its own workbook (e.g. when a project contains several
   resources/POUs).

4. **CSV output** — `--format csv` to emit one CSV file per globalVars
   section, for tools that cannot read `.xlsx`.

5. **GUI drag & drop (long-term)** — a small GUI (e.g. tkinter or a web UI)
   for drag-and-drop conversion for non-CLI users.