My free HR dashboard makes one small, specific promise: when a row in your Excel file is invalid, the import report gives its exact line number, the one Excel shows. While recording a demo, I fed it a file whose row 348 was wrong. The report said row 347.
This post covers why that happened, the few lines that fixed it, and the tests that keep it from coming back. The code comes from HR Dashboard Lite, a free FastAPI and Pandas project (MIT).
A realistic export
HR exports are rarely clean tables. The file I used has a title line, a blank line, then the header, then the data:
| Excel row | Content |
|---|---|
| 1 | Fichier du personnel — données fictives (title) |
| 2 | (blank) |
| 3 | Matricule, Nom et prénom, Date d'embauche, Date de départ… (header) |
| 4 | first employee |
| 348 | hired 14/06/2021, left 01/03/2019: impossible, so the row must be rejected |
The importer detects the header on its own, so the user never has to say where the data starts. That detection is where the line numbers went wrong.
The bug: blank rows vanished before anyone counted
The sheet is read with header=None, then cleaned up. Dropping fully empty rows looked harmless:
# before raw = raw.dropna(how="all").reset_index(drop=True) header_row = _find_header_row(raw) frame = _apply_header(raw, header_row) Later, the report turned a position in frame into an Excel row number with arithmetic that assumed nothing had been removed:
excel_row = frame.index + header_row + 2 reset_index(drop=True) had just renumbered everything from zero, so the blank row 2 was gone from the count. Every row after it was reported one line too early. With three blank rows, three lines too early. A file without blank rows gave correct numbers, which is why my own tests passed.
The fix: remember where each row came from
I still drop the empty rows, but I keep their original positions before resetting the index, and carry them all the way to the report:
# after raw = raw.dropna(how="all") # the index still holds each row's position in the sheet positions = raw.index.to_numpy() raw = raw.reset_index(drop=True) header_row = _find_header_row(raw) frame = _apply_header(raw, header_row) excel_rows = [int(positions[i]) + 1 for i in frame.index] The table object now carries excel_rows, and the validation step reads from it instead of computing a number:
excel_row = pd.Index(excel_rows) if excel_rows is not None else frame.index + header_row + 2 The old formula stays as a fallback for callers that pass a compact table. The rule I took from this: a row number is data, so it should be stored, not recomputed after the data has been reshaped.
The tests that pin it down
The test builds a workbook cell by cell, so the Excel row numbers are explicit, with blank rows before the header and between data:
def test_error_row_is_exact_with_blank_rows_before_and_between_data(): content = _xlsx({ 1: ["Export du personnel"], # row 2 is blank 3: HEADER, 4: ["A1", "2020-01-01", None, "F"], # rows 5 and 6 are blank 7: ["A2", "2021-05-01", "2019-01-01", "H"], # exit before hire: Excel row 7 8: ["A3", "2022-02-01", None, "F"], # row 9 is blank 10: ["A4", "not a date", None, "F"], # invalid hire date: Excel row 10 }) table = read_table(content, "export.xlsx") assert sorted(e["row"] for e in _errors(table)) == [7, 10] I added the same check for a compact sheet, and for a CSV with a title line and blank lines. The CSV case failed at first for a different reason, covered next.
Two more bugs, found by actually using it
Recording a phone-sized demo exposed two more problems that no unit test had caught.
A CSV with a title line crashed. pd.read_csv infers the number of columns from the first line, so a one-column title above a four-column header raised a ParserError. The fix is to give pandas the width of the widest line:
width = max((len(row) for row in csv.reader(io.StringIO(text), delimiter=delimiter)), default=1) pd.read_csv(io.StringIO(text), sep=delimiter, header=None, dtype=object, skip_blank_lines=False, names=range(width)) The column-mapping screen overflowed on phones. The page switched to a single column with grid-template-columns: 1fr, but 1fr means minmax(auto, 1fr). A wide preview table inside the card set the minimum width, so the whole card grew past the screen and the dropdowns were cut off. One line fixes it:
.import-grid { grid-template-columns: minmax(0, 1fr); } A small CSS test now checks that line, so a later refactor cannot quietly undo it.
What I would do again
- Test with the ugly file first. My early tests used tidy tables, which is exactly the input that hides this bug.
- Store identity, don't derive it. If users will see a row number, carry it with the row instead of rebuilding it after the data changed shape.
- Use your own tool on a phone and on a messy export. Two of the three bugs here only showed up that way.
The free Lite edition (MIT) is on GitHub: Adel1637/hr-dashboard-lite. It includes the fixes above and 49 automated tests.
