Saturday, May 31, 2025

Excel Data To JSON with schema Validation

 import pandas as pd

import json

from jsonschema import validate, ValidationError


def insert_nested_key(obj, key_path, value):

    keys = key_path.split(".")

    for key in keys[:-1]:

        obj = obj.setdefault(key, {})

    obj[keys[-1]] = value


def validate_json(obj, schema):

    try:

        validate(instance=obj, schema=schema)

        return True, None

    except ValidationError as e:

        return False, e.message


def excel_to_nested_json_with_validation(

    input_excel_path, schema_path, output_json_path="validated_output.json"

):

    df = pd.read_excel(input_excel_path)


    # Load JSON Schema

    with open(schema_path, "r") as f:

        schema = json.load(f)


    records = []

    errors = []


    for idx, (_, row) in enumerate(df.iterrows()):

        json_obj = {}

        for col, val in row.items():

            insert_nested_key(json_obj, col, val)


        is_valid, err_msg = validate_json(json_obj, schema)

        if is_valid:

            records.append(json_obj)

        else:

            errors.append((idx + 2, err_msg))  # +2 for Excel row number


    # Write valid records to file

    with open(output_json_path, "w") as f:

        json.dump(records, f, indent=2)


    print(f"✅ {len(records)} valid records saved to {output_json_path}")


    if errors:

        print(f"❌ {len(errors)} invalid record(s) found:")

        for row_num, msg in errors:

            print(f"  - Row {row_num}: {msg}")


if __name__ == "__main__":

    excel_to_nested_json_with_validation(

        "your_excel_file.xlsx",

        "Output_Schema.json"

    )


No comments:

Post a Comment