DEV Community

Yalakala-Naveen
Yalakala-Naveen

Posted on

Every Excel export you receive is broken the same five ways

Every Excel export you receive is broken the same five ways

The file lands at 4:58 PM. The analysis is due at 6.

Nothing is wrong with the data. Everything is wrong with the file.

There is a title row merged across the top. A blank row under it. A header split over two rows, with "Q1" merged across three columns. A totals row baked into the bottom of the data. A footnote sitting where a data row should be.

pandas.read_excel reads all of it exactly as instructed — which is the problem. Pandas does not detect any of this structure. You must already know the mess before you can describe it with header=, skiprows= and usecols=. So the columns come out named Unnamed: 0 through Unnamed: 7, the totals row sits inside your sums quietly doubling them, and the footnote becomes somebody's region.

Every analyst I know has the same drawer of glue for this. I had it too:

  • skiprows=3 for the finance export — until March, when someone inserted a row into the title block and the script silently read the wrong header for a week.
  • header=[0, 1] plus a hand-written flatten function for the two-row headers, with an Unnamed: filter because Excel stores a merged cell's value only in its top-left corner.
  • An openpyxl pre-pass that walks ws.merged_cells.ranges, unmerges everything and fills the top-left value across, because ffill after the fact guesses wrong on horizontal merges.
  • A row filter that hunts the word "Total" so the totals row stops inflating the numbers.
  • A prayer for the footnote rows.

Five fixes. Rewritten per file, per sender, forever. pandas recorded 688 million downloads last month and openpyxl 349 million — this hour of glue is being re-lived at industrial scale, and the feature request for pandas to fill merged areas on read (pandas #63684, opened January 2026) is still sitting open, because it does not exist.

So I stopped rewriting the glue and built the detector.

tidyexcel

import tidy_sheet

df, report = tidy_sheet.read_excel("q3-report.xlsx")   # or read_csv(...)

df.head()        # one header row, typed columns, no totals row
print(report)    # every fix, listed — nothing is changed silently
Enter fullscreen mode Exit fullscreen mode
tidyexcel report for q3-report.xlsx
  rows: 9 in -> 4 out (4 dropped)
  header: row(s) 3
  fixes:
    - title rows skipped: 1
    - blank rows skipped above the header: 1
    - totals row dropped (row 8): 'Total'
    - footnote row dropped (row 9): 'Note: Revenue excludes tax.'
Enter fullscreen mode Exit fullscreen mode

What it detects in v0.1.0:

  • Title / preamble rows above the header — it finds the real header row instead of being told where it is.
  • Merged header cells and multi-row headers — merged labels filled across their columns, then flattened to single names like Q1 / Sales.
  • Totals and subtotal rows inside the data — dropped, and named in the report.
  • Footnote / footer rows — Note:, Source:, sparse trailing rows below the data.
  • Merged data cells — the value filled across the merged range.
  • Numbers stored as text — converted to numeric, except values with leading zeros (zip codes stay zip codes).

The part I care about most is the report. Every fix is listed with the original row numbers. If a file was already clean, report.fixes is empty and the frame comes through untouched — there is a control-file test in the repo that fails the build if a detector ever misfires on a clean file. Deterministic heuristics over the raw cell grid: no LLM, no model calls, no network.

Try it before you install anything: the live demo runs the same detection in your browser on sample files or your own — nothing is uploaded anywhere.

pip install tidyexcel
Enter fullscreen mode Exit fullscreen mode

Repo: https://github.com/Yalakala-Naveen/tidysheet (MIT, Python ≥ 3.10, pandas + openpyxl).

What it deliberately does not do

  • No type inference beyond numbers-stored-as-text. Dates stay as the values openpyxl/pandas give you.
  • One sheet per read (the active sheet by default; pass sheet="Name" or an index).
  • .xlsm and .csv are supported; legacy .xls is not.
  • The header search covers the first 50 rows and at most a 3-row header block. A file whose "data" is all text with repeated values can fool the multi-row-header rule — and when it does, the report shows the decision it made, so a wrong call is visible, never silent.

If you feed it a file shape it gets wrong, open an issue with the file's structure (not the data) — the detectors only get better on real shapes. The merged-cell fill pandas users have been asking for since January is the first thing it does; tell me what your sender's files do that it misses.

Top comments (0)