Validating Row Counts After an Excel Import Step by Step

An import that finishes without an error has not been shown to be correct. The classic silent failures leave the row count short: a blank row in the middle that ended the read, hidden rows that a tool skipped, a type guess that turned text into nulls, a WHERE clause that dropped what it should have kept. The check that catches them is the oldest in data work, counting, and it works only if it is done at the right places against independent numbers. This post lays out those places and the queries, and builds a reconciliation that fails when anything goes missing. It belongs to the cluster on getting Excel data into a database.

Three counts that must reconcile

An import has three stages, and each has a count.

1. Source rows: the number of data rows in the sheet.

2. Staged rows: the number of rows in the staging table for this batch.

3. Target rows and rejects: the rows inserted into the final table plus the rows sent to the rejects table.

The rules between them are simple:

• staged rows must equal source rows;

• target rows plus rejected rows must equal staged rows.

If the first fails, the read lost or invented rows. If the second fails, the conversion lost rows, which is a bug, because a row must either land or be rejected with a reason. Nothing may disappear in between. Writing the rules like this removes the argument about whether a difference matters: any difference is a failure to investigate.

Getting the source count independently

The risk in comparing the staged count with a number from the same loader is that both share the same mistake. If the loader's reader stops at the first blank row, it counts only the rows before it, and the stage agrees with it perfectly. The source count must come from somewhere else. There are four honest sources, in decreasing order of strength:

The sender's figure. A control total supplied with the file: "845 orders, total 41,230.50". It is the strongest check because it comes from the system the data was exported from. Ask for it in the file name, an email or a manifest. A footer row in the sheet works too, if you parse it deliberately.

A second read with different code. Count the rows with a different method than the loader uses. If the loader uses pandas, count with openpyxl:

from openpyxl import load_workbook

def count_data_rows(path, sheet, header_row=1, key_col=1):
    wb = load_workbook(path, read_only=True, data_only=True)
    ws = wb[sheet]
    n = 0
    for row in ws.iter_rows(min_row=header_row + 1, values_only=True):
        if row[key_col - 1] not in (None, ""):
            n += 1
    return n

print(count_data_rows("orders.xlsx", "Orders", header_row=5))

845

The function counts rows after the header whose key column is not empty. Using a key column, such as the order number, avoids counting stray formatted rows. Do not use ws.max_row: it comes from the sheet's declared dimension and includes formatted empty rows, so it is often larger than the real count (why Excel files get huge).

The workbook itself. Open it, select the key column and read the count shown in the status bar, or use =COUNTA(A6:A2000) in a spare cell. This is a manual check, useful the first time and for one-offs.

The sheet's own total. If the report prints "Total rows: 845", compare with it, taking care that it measures what you think it does.

Record the source count and where it came from. A count with no source is an unverified number.

Counting the stage and the target

In SQL, the staged count is one query:

SELECT count(*) FROM stage_orders WHERE batch_id = 42;

The reconciliation of the second rule is one query too, and it is worth turning into a view so it runs on every batch:

SELECT s.batch_id,
       s.staged,
       coalesce(t.inserted, 0) AS inserted,
       coalesce(r.rejected, 0) AS rejected,
       s.staged - coalesce(t.inserted, 0) - coalesce(r.rejected, 0) AS unaccounted
FROM (SELECT batch_id, count(*) AS staged FROM stage_orders GROUP BY batch_id) s
LEFT JOIN (SELECT batch_id, count(*) AS inserted FROM orders GROUP BY batch_id) t USING (batch_id)
LEFT JOIN (SELECT batch_id, count(*) AS rejected FROM rejected_rows GROUP BY batch_id) r USING (batch_id)
WHERE s.batch_id = 42;

 batch_id | staged | inserted | rejected | unaccounted
----------+--------+----------+----------+-------------
       42 |    845 |      842 |        3 |           0

unaccounted must be zero. This assumes the final table has a batch_id column recording the batch that inserted each row, which is useful in itself, and that one source row yields one target row. If a sheet row can produce several target rows (a wide row split into many), or none for a legitimate reason such as a blank separator, count the cases explicitly in the rules, or the check will fail for the right reasons and still be noise.

When an upsert replaces existing rows, the number of inserted rows is not the number of new rows. Count the rows the statement touched (INSERT ... RETURNING or the statement's row count) and compare that, not the table's total. The table's total can legitimately stay the same on a reload.

Control totals

Equal counts do not prove that values survived. A value can be altered, or one row dropped while another duplicated. A control total catches those. Pick a numeric column that matters, usually the amount, and compare its sum at the source and the target:

SELECT sum(amount_eur) FROM orders WHERE batch_id = 42;

Against the sender's total, or a sum computed in the second read of the sheet:

total = 0
for row in ws.iter_rows(min_row=6, values_only=True):
    if row[0] not in (None, ""):
        total += float(row[2] or 0)
print(round(total, 2))

Compare after rounding to the cent. Sums of floats from the sheet may differ from the exact decimal sum in the last digits, so a difference smaller than half a cent is not a failure; more than that is. Add counts per category for a second angle: the number of rows per status, per region or per month. A dropped or duplicated row shifts one of those counts, and a distribution check often points straight at the culprit. Include a hash of the key column if you want a stronger check, for example the sum of hashtext(order_no) values in SQL against the same in Python, though a count and a total are enough for most.

Where the differences come from

When a count is off, the cause is usually one of a short list.

Duplicates deserve a note. If the key is not unique in the sheet, an upsert collapses two rows into one and the count of inserted rows is lower than staged without any rejection. Find duplicates in the stage before converting (staging Excel data in a table before cleaning it) and decide whether they are errors.

Make the check part of the job

A check that someone has to remember to run will be skipped on the day it matters. Put it in the loader and have it stop the batch:

def reconcile(cur, batch_id, source_rows):
    cur.execute("select count(*) from stage_orders where batch_id = %s", (batch_id,))
    staged = cur.fetchone()[0]
    if staged != source_rows:
        raise RuntimeError(f"staged {staged} rows, source has {source_rows}")
    cur.execute("""
        select (select count(*) from orders where batch_id = %s)
             + (select count(*) from rejected_rows where batch_id = %s)
    """, (batch_id, batch_id))
    accounted = cur.fetchone()[0]
    if accounted != staged:
        raise RuntimeError(f"{staged - accounted} rows are unaccounted for")

Run reconcile after the staging step and again after the conversion, inside the same transaction as the conversion, so a failure rolls the target changes back. Record the numbers in the batch table whether or not they pass:

UPDATE load_batches
SET source_rows = 845, staged_rows = 845, inserted_rows = 842, rejected_rows = 3, control_total = 41230.50
WHERE batch_id = 42;

A history of those columns shows trends: a file whose row count fell by 40 percent from the previous delivery is suspicious even when every check passes. Add a soft alert for a change beyond a threshold you choose, and let a person look.

What a count cannot tell you

Counting finds missing and extra rows. It does not find wrong values that happen to keep the totals intact: two amounts swapped, a customer name cut short, a date shifted by a day. For those, sample. Pick twenty rows at random plus the first and last, and compare them field by field with the sheet. Do it by eye the first time you load a new kind of file, and keep the sample query:

SELECT * FROM orders WHERE batch_id = 42 ORDER BY random() LIMIT 20;

Then add targeted checks for the fields that matter: the minimum and maximum of each date column, the length range of each text column, and the set of distinct values in each category column. A date column whose minimum is 1900 or whose maximum is next century has been converted wrongly (importing Excel dates correctly into a date column).

When row counts alone are not a sufficient check

The same row count can hide wrong data. A value corrupted by a type cast keeps the count intact. Add control totals and sampling.

The sheet's count is itself unreliable. A manually maintained count in a cell may be stale. Prefer the sender's system of record.

Rows legitimately merge or split. Pivoted sheets, header-detail layouts and repeated blocks do not map one to one. Define the expected relationship and count against it.

Late corrections. A later file that replaces earlier rows changes the target's total. Reconcile per batch, not against the table's grand total.

openpyxl describes row iteration in its optimized modes page, and PostgreSQL's aggregate functions are listed in the aggregate functions reference. To check a small extract as JSON, use the CSV to JSON converter.