An email went out from the office with 1 file attached. It was a spreadsheet template I made myself, with 7 columns: date, description, account code, debit, credit, counterparty, reference number. It was there so people could move their existing books into Books, and over 2 months I sent it to nine people. What I am writing about today is that the file was the first trap.
Nobody starts from a blank page. Someone beginning with Books usually has 1 or 2 years of records in a spreadsheet. We cannot ask them to type all of that again, so we built an import and handed out a template for the shape we wanted it in. We decide the shape, so we can make the shape a safe one. That is how I was thinking about it.
In the date column, the sample row reads 2026-01-05. I set no format on the column. Written year, then month, then day, there is only 1 way to read it, whoever is looking. So I did nothing further. I thought the template's work was done.
A spreadsheet treats what is in that cell as a date. What it treats as a date, it displays according to the settings on that machine. Most of the machines in use here are month/day/year, and some are day/month/year. And when the file is saved as CSV, what gets written is the characters that were on the screen. 2026-01-05 comes out as 1/5/2026. The spreadsheet is not doing anything out of turn. That is its job.
Our import accepted exactly 2 forms as a date: yyyy-mm-dd and yyyy/mm/dd. 1/5/2026 is neither of those 2. A row whose date it could not read, the import dropped. It did not refuse the file and stop; it dropped the row quietly and carried on. And then it put on the screen: 1,336 rows imported.
That screen counts only what went in. What did not go in is counted nowhere. So a file that has lost rows wears the same face, on screen, as a file that had fewer rows to begin with. Neither the person importing nor we had anything to go on that would say the number was short.
It was not us who found it. In the third month of use, someone reconciling a supplier's balance noticed that 103,250 pesos were missing. We were shown the original file that same day. It had 1,412 rows. The number left on the import screen was 1,336. 76 rows had gone without so much as an error.
The reason the whole file did not drop is that the rows had been made in 2 ways. Rows pasted in from an older file came in as text, and that column never became a date column, so they stayed as 2026-01-05. Rows typed in by hand afterwards were treated as dates and turned into month/day/year. What had been dropped were the rows typed by hand.
This is the part that hurt. What gets pasted in bulk is the old history; what gets typed by hand is the recent months. Recounting across all nine, 11,200 rows had gone in and 418 rows had been dropped. Of those, 356 fall in the last 3 months of each person's file. What had disappeared was the newest part, the part you actually reconcile against.
The repair split in 2. On the import side, we stopped dropping. If even 1 row cannot be read, nothing from that file goes in. The screen now always shows both numbers side by side: the rows in the file and the rows accepted. For a row it could not read, it gives the row number and what the cell actually said, in the characters it said it in.
On the template side, the date column is now held as text, the sample row is entered as text, and the format is written under the heading. But I cannot write that this has made it safe. A file we hand out stops being ours the moment it is opened. A spreadsheet does its own job without knowing what we intended. A rule cannot be placed on the side of a file we have already given away.
That is what I had the wrong way round. The shape we wanted was decided in the template, and the import was written as though that shape would arrive. I had taken the place where the shape is decided and the place where it is checked to be one place. Checking can only live on the side where the data arrives. What a template can do is make a mistake less likely; it is not a guarantee that none was made.
I estimated half a day and it took 6 days. Making the import stop was 2 hours. The rest was getting the original file back from all nine of them and putting the 418 dropped rows in again. Two people had changed their original file since we handed it out, so we went row by row with them to see which ones were not in. Since then the import has stopped 31 files outright. Rows dropped quietly: 0. I sent it to the engineer on the KAZENA side as well, and they wrote back that they hand out the same template and have changed the column's format to text.