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