Saturday, May 31, 2025

Excel Data to JSON Schema Validation with Report

 import pandas as pd

import json

import csv

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_validated_json(

    input_excel_path,

    schema_path,

    output_json_path="validated_output.json",

    error_report_path="validation_errors.csv"

):

    df = pd.read_excel(input_excel_path)


    # Load JSON Schema

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

        schema = json.load(f)


    valid_records = []

    error_records = []


    for index, row in df.iterrows():

        json_obj = {}

        for col, val in row.items():

            insert_nested_key(json_obj, col, val)


        is_valid, error = validate_json(json_obj, schema)


        if is_valid:

            valid_records.append(json_obj)

        else:

            error_records.append({

                "Excel Row": index + 2,  # Excel row number (1-based + header)

                "Error Message": error

            })


    # Save valid JSON objects

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

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


    print(f"✅ Saved {len(valid_records)} valid JSON object(s) to: {output_json_path}")


    # Save validation error report

    if error_records:

        with open(error_report_path, "w", newline='') as csvfile:

            fieldnames = ["Excel Row", "Error Message"]

            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

            writer.writeheader()

            for error in error_records:

                writer.writerow(error)


        print(f"❌ Found {len(error_records)} invalid row(s). Errors saved to: {error_report_path}")

    else:

        print("✅ No validation errors found!")


if __name__ == "__main__":

    excel_to_validated_json(

        input_excel_path="your_excel_file.xlsx",

        schema_path="Output_Schema.json"

    )


No comments:

Post a Comment