School and employee transport operators do not send you a schema. They send the workbook the school or the factory already had. The column titles are whatever that office typed. Sometimes there is no header row. Sometimes the student list, the school address, and the vehicle list are three different sheets.
We used to solve this by asking the operator to rebuild the file into a template. That failed on the morning a shift list arrived half an hour before the buses left. There was no time to rename columns. This post is about the reader we built instead, and the parts we refused to let a model decide on its own.
The operational version of that morning is here: Import students from Excel without rebuilding your spreadsheet.
The file is not the record
An .xlsx, .xls, or .csv up to 8 MB is accepted in the browser and parsed with SheetJS into a matrix per sheet. PDFs and photos are rejected there. The interesting work starts after the matrix exists.
Each sheet is classified before anyone tries to read passengers out of it:
- A short two-column sheet whose labels look like "school", "address", or "phone" is treated as key/value metadata, not as people.
- A sheet whose headers and first rows look like a roster is the passenger list.
- A sheet whose headers look like route name, plate, or driver is a service list. Those rows must not become passengers.
- Anything else is left alone.
That classification is local and boring on purpose. A model is a bad place to decide whether a sheet is a list of buses or a list of children.
The server also stops after 400 data rows and reports that the rest of the sheet was not read. A roster of a few thousand people has to be split. We would rather show a short warning than silently drop the tail.
Two extractions, then a comparison
The same matrix is read twice.
The local reader maps headers with a fixed alias list, including the Turkish labels we actually receive (öğrenci, veli, adres, sınıf). It pulls a name, a school, an address, parent contacts, and two service fields: who the person rides with in the morning, and who they ride with in the afternoon. Those are not the same column. A single "bus" field throws away the case where two children share a morning service and go home on different ones.
In parallel, the server sends the workbook to a model. It does not send the xlsx. It sends a TSV rendering of each sheet, with the sheet name and the kind we already guessed. The prompt is a JSON shape: schools, passengers, routes, warnings. Empty cells stay empty. The model is told not to invent a name, a phone, an address, or a school. Warnings have to be sentences an operator can read, not schema jargon.
The model result is kept only when it is richer than the local one. Richer means, in order: more passengers, then more parent contacts, then more rows that carry a service name, then more schools. A tie keeps the local reading. If the model errors, times out, or returns an empty passenger list, the local reading is what the operator sees.
function preferModel(model, local) {
if (model.passengers !== local.passengers) return model.passengers > local.passengers;
if (model.parents !== local.parents) return model.parents > local.parents;
if (model.routes !== local.routes) return model.routes > local.routes;
if (model.schools !== local.schools) return model.schools > local.schools;
return false;
}
This is the whole policy. We do not average the two readings and we do not ask the model to "fix" the local rows. One extraction wins, and the other is discarded.
The browser does not wait for the model
The page parses the file locally as soon as it is dropped. If that parse already found passengers, the UI waits about eight seconds for the server reading. If the local parse found nobody, it waits longer, about twenty seconds, because the model is the only chance on a layout the aliases do not recognize. When the wait ends, the request is aborted and the review screen opens on whatever extraction is in hand.
The server itself will keep an OpenAI call open for up to 45 seconds. That bound is for the server, not for the person staring at the upload. A late model response must not block the review of a file the local reader already understood.
Nothing is written until a person confirms
The extraction is a proposal. The review screen shows the passengers, the school, the parent contacts, and the service names. Rows can be edited or removed. A missing school can be typed, chosen from the account, or left as "Default School" so the import can continue.
If the operator is signed in, each incoming row is compared with students already in the account. The name is normalized (Turkish characters folded, punctuation stripped). An exact normalized name is only a candidate. A same school, a same parent phone, or a same student number raises the score. The result is a label on the row, not a merge. The operator chooses to keep the existing record, refresh it from the file, or add a second person. A later file that simply omits someone does not delete them. This is an import, not a sync.
Service names are kept as the operator wrote them. A named morning or afternoon service is created once, with everyone assigned to it, even when that group is larger than the capacity number used for unnamed passengers. The overflow is reported. The group is not split, because the name was a decision the transport office had already made. People with no service name are the ones grouped by school and capacity.
Addresses are geocoded when the save actually runs, in small chunks so the UI can show progress. A failed geocode is reported. A successful geocode is still only a point on the street. The school's bus gate is often not the postal address, and the stop order is not chosen here. Routing is a later step, after the records exist.
What we left out
We do not ask the model to write the database. We do not treat a header alias as proof that the column is right. We do not turn a 400-row cap into a silent success. The review exists because both readers are wrong sometimes, and the person who received the file is the one who knows which rows are real.
If you want the operator-side version of the same work, it is the post linked at the top.
Top comments (1)