Bulk imports that report row-level errors instead of failing at row 4,000

Bulk imports that report row-level errors instead of failing at row 4,000

A school wants to migrate 6,000 student records from their old system. They export a spreadsheet, upload it, and the import crashes at row 4,000 because one cell has text where a number belongs. The transaction rolls back. The admin re-exports, fixes what they think is wrong, uploads again, and hits a different failure at row 2,100. This can go on for days.

The fix is not to make the import more forgiving. It is to validate every row before you insert anything, collect all the failures, and hand the user a report that says row 12,847 has an invalid date and row 15,003 references a class ID that doesn't exist. Then let them fix the spreadsheet and try again.

We do this in two passes. First, stream the file and validate every row against a schema and against the database's current state. Collect errors keyed by row number. If there are errors, stop — insert nothing. If there are no errors, proceed to the insert pass. The tradeoff is memory and time: you read the file twice, or you hold the validated rows in memory. For files under 50,000 rows, holding them in memory is fine. For larger files, write validated rows to a temporary table and insert from there.

The validation itself is row-level and deterministic. Each row produces either a clean record or a list of field-level errors. A row with three bad fields reports three errors, not one.

from pydantic import BaseModel, ValidationError, field_validator
from datetime import date

class StudentRow(BaseModel):
    admission_no: str
    name: str
    class_id: int
    dob: date

    @field_validator("admission_no")
    def no_duplicates(cls, v, info):
        seen = info.context.get("seen_admission_nos", set())
        if v in seen:
            raise ValueError(f"Duplicate admission_no {v} in this file")
        seen.add(v)
        return v

The validator alone catches format errors. Reference errors — does class_id 8 actually exist — need a separate check against the database. We load the relevant lookup tables once at the start, not per row. For a school import, that means fetching all class sections, all house names, and all existing admission numbers into sets. Checking membership against a set is O(1); querying per row is O(n) database calls and will time out.

The error report is the part users actually care about. We return JSON structured by row number, not a flat list.

{
  "12": ["dob: not a valid date"],
  "847": ["class_id: no section with id 14"],
  "903": ["admission_no: already exists in the system",
           "name: field required"]
}

For the frontend, we render this as a table with row numbers and error messages, sortable by error count. The user downloads the original spreadsheet, searches for the row numbers, fixes them, and re-uploads. They do not re-export from the source system. They do not start over.

One thing we learned the hard way: row numbers in the error report must match the spreadsheet row numbers, including the header row. If the file has a header on row 1 and data starts on row 2, a validation error on the first data row should be reported as row 2, not row 0 or row 1. Teachers and admins count rows in Excel, not in zero-indexed arrays. Getting this wrong means the user fixes the wrong row, re-uploads, and the error moves or persists. They lose trust in the report.

The other thing: do not silently skip rows that fail validation and insert the rest. Partial imports are worse than failed imports. The user now has 5,997 records in the system and 3 missing, and they don't know which 3. Either everything goes in or nothing does. If the user wants to import the good rows and fix the bad ones later, make that an explicit choice with a second button, not the default behavior.

The cost of this approach is complexity in the import handler. You write a validation layer, a lookup cache, and an error formatter. For a one-time migration of 200 rows, it's not worth it. For an import the client will run weekly — new admissions, staff updates, fee structures — it pays for itself the first time someone uploads a file with 40 errors and gets a actionable report instead of a stack trace.