JSON ↔ Excel Mapping Tool

This tool converts between JSON Schema, JSON Instances, and Excel mapping templates.
It supports three modes:

1. Schema → Excel — Generate a blank Excel mapping template from a JSON Schema.
2. Instance → Excel — Flatten an existing JSON instance into an Excel mapping.
3. Excel → Instance — Reconstruct a JSON instance from a filled-in Excel mapping.

---

Folder Structure

json-excel-mapper/
├── files/
│   ├── input/          # JSON schemas and filled-in Excel templates
│   └── output/         # Generated Excel templates and JSON instances
├── src/                # Source code
│   ├── schema_to_excel.py
│   ├── instance_to_excel.py
│   └── excel_to_instance.py
└── run.py              # Entry point

---

Setup

1. Create and activate a Python virtual environment (recommended):

   python -m venv .venv
   source .venv/bin/activate   # Linux / macOS
   .venv\Scripts\activate      # Windows

2. Install dependencies:

   pip install -r requirements.txt

---

Usage

python run.py

Select a mode when prompted:

Choose an action:
1 - Convert JSON schema(s) to Excel
2 - Convert JSON instance(s) to Excel
3 - Convert Excel(s) to JSON instance(s)
Enter 1, 2, or 3:

Then enter the input file or folder path and the output folder path (press Enter to accept the defaults):

Enter input file or folder path [files/input]:
Enter output folder path [files/output]:

You can provide a single file or a folder. When a folder is given, all matching files in that folder are processed in one run.

Examples

Mode	Input	Output
Schema → Excel	files/input/0.7.0/DigitalProductPassport.json	files/output/0.7.0/DigitalProductPassport_mapping.xlsx
Instance → Excel	files/input/0.7.0/sample_instance/DigitalProductPassport_instance.json	files/output/0.7.0/sample_instance/DigitalProductPassport_instance_mapping.xlsx
Excel → Instance	files/input/NATA_POC/PoC_DigitalProductPassport_mapping.xlsx	files/output/NATA_POC/PoC_DigitalProductPassport_mapping_instance.json

---

Excel Column Reference

Schema mapping template (modes 1 and 3)

Column	Description
Path	Dot-notation path to the field (e.g. credentialSubject.id, issuer.issuerAlsoKnownAs[0].name). Do not edit.
Type	Field type: string, integer, number, boolean, array, object, ref. Do not edit.
Required	Yes if the field is mandatory per the schema, No if optional. Do not edit.
Description	Human-readable description from the schema. For enum fields, lists the allowed values. Do not edit.
Ref	Internal schema reference ($ref) if applicable. Do not edit.
Writable	Yes if you should enter a value directly in this row; No for structural/container rows. Do not edit.
Value	Fill this in. Enter the value for this field. Leave empty to omit the field from the output.
Comment	Optional free-text notes for your own reference. Does not affect JSON output.

Instance mapping (mode 2)

Column	Description
Path	Dot-notation path to the field.
Type	Detected type: string, integer, number, boolean, array.
Value	The value from the JSON instance.

---

Colour Coding

Colour	Meaning	What to do
Dark blue (header row)	Column headers	Do not edit
Light yellow	Required + writable field	Must fill in — mandatory per the schema
Light blue	Optional + writable field	Fill in if you have the data; leave blank to omit
Light grey	Structural row (Writable = No)	Do not edit — container rows used to build JSON structure
White	Value cell of any writable row	Type your value here — the white cell is the editable input

---

Instructions for Filling in an Excel Template

These instructions apply to Excel templates generated from a JSON Schema (mode 1).
Only edit the Value and Comment columns. Do not change any other column.

Step 1 — Understand which rows to fill in

• Rows where Writable = Yes are leaf fields that accept a direct value. Fill these in.
• Rows where Writable = No are structural (object/array container) rows. Leave these blank — the tool builds the JSON structure automatically.
• Rows where Required = Yes must have a value for the output to be a valid JSON instance.
• Rows where Required = No are optional. Leave them blank to omit them from the output.

Step 2 — Enter values in the correct format

Type	What to enter in the Value cell	Example
string	Plain text	did:web:example.com
integer	A whole number	42
number	A decimal or whole number	1.5
boolean	true or false	true
array (of strings)	A JSON array	["value1", "value2"]
array (single value)	Plain text — it will be wrapped automatically	https://example.com/scope

Step 3 — Check allowed values for enum fields

When the Description column contains Must be one of: ..., your value must be one of the listed options exactly as written.

Example — credentialSubject.assessmentLevel:

Must be one of: authority-benchmark, authority-mandate, authority-globalmra,
authority-peer, authority-extended-mra, scheme-self, scheme-cab, no-endorsement

Step 4 — Handling repeated array items

The template defines one example item per array using [0] notation
(e.g. issuer.issuerAlsoKnownAs[0].id).

To add more items to the same array, insert new rows below the [0] block
and use [1], [2], etc. in the Path column:

issuer.issuerAlsoKnownAs[0].id     →  https://abr.business.gov.au/ABN/View?id=123
issuer.issuerAlsoKnownAs[0].name   →  Company A
issuer.issuerAlsoKnownAs[1].id     →  https://abr.business.gov.au/ABN/View?id=456
issuer.issuerAlsoKnownAs[1].name   →  Company B

Step 5 — Generate the JSON instance

Once the template is filled in, run the tool and select option 3 — Convert Excel(s) to JSON instance(s):

python run.py
# Choose: 3
# Input:  path/to/your/filled_template.xlsx
# Output: path/to/output/folder

The tool will:

• Skip any row with an empty Value cell.
• Skip optional sections where none of the required fields have been filled in.
• Output a .json file named after the Excel file.