The OCR text of my sample score sheet had every 18-digit ID right. The .xlsx I built from it had 7 of the 8 IDs wrong, and all 8 student codes had lost their leading zero. Nothing was misread on the way. I wrote the IDs into the file as numbers, and that one choice did the damage.
The OCR text was already fine
The sample is a made-up score sheet: 8 students, 7 columns, a 7-digit code that starts with 0, an 18-digit ID number, and one ID that ends in X. I ran it through ImgIng (https://imging.ai/) in a desktop browser with the default Professional model, and worked from the text it copies out. It only runs on a desktop, it doesn't read Korean, and the numbers still need checking by eye.
The copied text separates columns with tabs, but not all of them. In my 8 data rows the English score and the total are joined by a space, and in four of them the name and the ID share a cell too, so I get 5 or 6 cells where the table has 7. I haven't pasted that text into a spreadsheet, so I can't say what Excel does with it. Because positions looked shaky, I pulled the IDs out by shape instead of by column. \d{17}[0-9X] found 8 IDs and 0\d{6} found 8 codes, and both lists matched my ground-truth CSV exactly.
The same 16 cells, written four ways
I wrote the code and ID columns (8 rows, 16 cells) with openpyxl 3.1.5 on Python 3.9.6, then read the file back and compared with the source strings.
| How the cell was written | Cells that differ after reading back |
|---|---|
Python int
|
15 of 16 |
Python float
|
15 of 16 |
int, then number format set to text (@) |
15 of 16 |
str, with or without @
|
0 of 16 |
The 15 are the 8 codes (0230101 came back as 230101) and 7 IDs that came back as floats. The ID ending in X survived only because it was never a number.
Passing an int didn't help. Inside sheet1.xml, the first ID was stored as <v>4.401062010041836e+17</v>, so the file never held the 18 digits at all. Setting the text format afterwards just labels a value that was already rounded. The order is the whole story: decide the type before the write.
# what I did first
for code, pid in rows:
ws.append([int(code), int(pid) if pid.isdigit() else pid])
A double can't hold 18 digits
int(float("440106201004183613")) gives 440106201004183616. Across the 7 all-digit IDs, 7 came back different. A 64-bit double keeps roughly 15 to 17 significant digits, and 18 doesn't fit.
JavaScript has the same trap before any spreadsheet is involved. In node, JSON.parse on an array of the same 7 IDs written without quotes changed 7 of 7, and 440106201004183613 came back as 440106201004183600. If the OCR result goes through an API as JSON, the ID has to be a string there too.
What Excel adds on top
Microsoft's page on keeping leading zeros and large numbers says Excel stores 15 significant digits and turns anything after the 15th into 0, and that ID-like columns should be text. By that rule my first ID would show 440106201004183000 in a number cell. That is my reading of the docs. I didn't open any of these files in Excel, so I can't tell you what the screen shows.
What I changed
IDs, codes and barcodes stay str from the moment the OCR text is split until the file is written. Being .isdigit() isn't a reason to call int(). Then a check reads the file back and fails the job if any ID cell isn't a string of the right shape:
ok = isinstance(v, str) and re.fullmatch(r"\d{17}[\dX]", v)
On the int and float files this flagged 7 cells each. On the str file it flagged 0.
One question I haven't closed: when a plain CSV of these IDs is double-clicked open in Excel, does the ID column turn into numbers by itself? I haven't tested it. Until I have, the checklist is two lines: write strings, read them back.
What these numbers cover
One table, 8 rows, two columns. The IDs are random 18-digit strings and not valid ID numbers, and the sheet is clean and English-and-digits only. Everything above is about what openpyxl and node store, not about what any spreadsheet app displays.
Top comments (0)