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