Bad CSV and Excel uploads: 18 files tested

Our attendance program, Herewise, starts with a class list. A lecturer imports a roster of matric numbers and names for each level, from the university portal or from a spreadsheet the department keeps, as a CSV or Excel file, and everything after that hangs off it: fingerprints, scans, the attendance sheet, the list of who may sit the exam.

The first real run tripped at that first step. The portal’s roster is a .xls that is really a web page saved under a spreadsheet’s name, and the program said only that it could not read it. That was fixed in the next release. Then the owner asked the wider question: when a lecturer brings a file that is wrong, what should happen?

The obvious answer is a list of what importers usually do. We did not start there. We made eighteen wrong files and put each one through the importer, on a scratch register, before changing a line.

Eighteen wrong CSV and Excel files, and what the importer did

Thirteen of them did something we would not accept, in three different ways.

Three imported wrong data, or offered to:

A list with its two columns the wrong way round imported six students whose names were their matric numbers.
Another level’s list, with no level column, imported into the wrong level, offered to move all six students across. The only sign was a line on each row of the preview.
A file with five of its six rows refused offered to “Import 1 student”.

Five were refused, though nothing was wrong with them:

Surname and other names in two columns of their own.
A list separated by tabs.
A list separated by semicolons.
Excel’s “Unicode Text” export, which is UTF-16. The importer read its first heading as “m a t r i c _ n o”.
A workbook with a cover sheet in front of the list.

Five said something true and useless, among them:

A PDF: “not readable text, save it as CSV”.
A Word document: “could not be read as a spreadsheet”.
A file of headings with nobody under them: “Nothing in this file is new”.
A 20 MB file stopped Python’s CSV reader with “field larger than field limit”, which reached the screen as a server error.

The error messages were the least of it. A refusal is at least honest. The three that went through were the dangerous ones, because a roster imported wrong does not look wrong. Six students named after their matric numbers would have had fingerprints enrolled, scanned in every week, and turned up wrong on the exam list at the end of term.

Handling an invalid upload: one answer per file

Every change answers one of those files. None came from a list of good practice.

Ask, do not refuse, when a file can be read but its columns cannot. Headings that name nothing, columns holding the wrong thing, or a workbook holding more than one list with none named for the level now come back as a question. The screen shows the first rows, and a guess made from the values themselves: a matric number has a digit and a slash or a dash, a name has letters and no digits. The lecturer corrects the guess and presses Preview. Nothing is imported on a guess, and a column is questioned only when more than half of it looks wrong, so one odd row is left to the row checks.

A name with a digit in it is not a name, and a matric number with no digit is not a matric number. A line reading “TOTAL 251” under the list used to go in as a student with no matric number. It is refused now, with what to type instead. Two of our own test files had made-up names with digits in them, and had to change.

A warning needs a tick that the server checks, not only the screen. Another level’s list, and a file where most rows were refused, both stay importable, because either can be right. But the Import button waits for a tick under the warning, and the server refuses the import without it. “Another level’s list” means three or more students, and more than half of those importable, already on a different level this session. Two students moving level on a corrected list is ordinary and is not warned about.

A surname kept in its own column stays in its own column. Joining SURNAME and OTHER NAMES and splitting them again would cut a two-word surname in half, so the parts are carried through as they came.

Everything else is read. Tabs, semicolons, UTF-16 and a cover sheet are handled without asking. A file that is not a roster says what it is: a PDF, a photo, a Word or PowerPoint file, a LibreOffice spreadsheet, a zip folder, or a workbook locked with a password, with how to unlock it. Anything over 5 MB is turned away before it is read. A blank template, named for the level, sits beside the file picker. And a refused file stays on screen until another is chosen; it used to be a message that vanished after six seconds.

Why not ask an AI to fix the file?

Partway through, the owner asked whether, when a file fails, the program should hand the lecturer a ready-made prompt to paste into an AI chat, so the AI could rewrite the file into the right format.

We said no, for four reasons.

The file is a list of students’ names and matric numbers. That is personal data about people who never chose this program. Our own rule while building it was that real student data is never pasted into a design tool or a prompt; the screens were designed with invented names. A prompt that tells a lecturer to paste the real list into a chat would break that rule for every file that fails.
An AI can change the data while fixing the format, and say nothing. Retyping 250 rows, it can drop a student or change a letter in a name. Its answer would then pass the importer, because the importer checks the shape of the file, and the shape is exactly what the AI fixed. A wrong letter in a name is still a valid name.
The program works offline, on purpose. The moment a file fails is not a moment anyone can promise an internet connection.
The column question does the same job on the laptop. It shows the rows and asks which column is which. The lecturer decides, and the data does not move.

What the real files did

The five real rosters a department gave us, 930 students across five levels, read exactly as they did before: none refused, no warnings, no questions. That was the check we cared about most, because a stricter importer that rejects the files people already have is worse than a loose one. The eighteen wrong files are kept as 45 tests, so none of them can quietly start working the old way again.

What we do not know yet

The thresholds, more than half and three students, are our judgement. No lecturer has yet met the column question with a file of their own.
A file that is wrong in a way we did not think of will still get through or be refused badly. The eighteen are the ones we could think of, and the first real run already taught us that the file people actually have is not the one you imagine.