Importing field data without creating duplicates

Data for education programmes is often collected in the field with tools such as KoboToolbox, then exported as a spreadsheet. Borno Agile MIS has to turn those exports into usable records: schools, students and the enrolments that link them.

The obvious import creates a row for every line in the file. That works exactly once. The moment an updated export arrives — a few new pupils, some corrected details — the same schools and students are created again, and every report built on them is wrong.

So the import treats each file as a set of facts about records that may already exist. For every row it looks for the school first. Field forms spell the same school in different ways, so the match is case-insensitive, and a school already seen during the import is taken from an in-memory cache rather than queried again. Only when no match exists is a new school created, with an ID built from its LGA, such as AGILE/JERE/00001.

Students are matched by email, in bulk: the import collects every email in the file and fetches the existing students in a single query instead of one query per row. A student created earlier in the same file is remembered too, so a duplicated line in the export does not create a second record. Existing records are updated; new ones are created; the result page reports how many of each, and how many rows were skipped.

Exports also carry fields the MIS has no dedicated column for — identity numbers, cohort, caregiver details. Rather than discarding them, the import keeps them alongside the student record, so nothing collected in the field is lost.

Finally, there is a downloadable template with every supported header. The cheapest import bug to fix is the one prevented by giving people the right file to fill in.