How to extract spreadsheet data with Python: Pandas vs document parsing

You have a spreadsheet, and you need to extract its content in Python. That sentence hides two different jobs. One is getting records out: typed values you can add up, join and load into a database. The other is getting the sheet out as a document: text you can read, search and hand to a language model. For the first job, use pandas. For the second, document parsing does something pandas doesn't, and it's worth knowing exactly what.

This post does both on the same workbook. First, use pandas the normal way, with pandas and openpyxl, including the three things that quietly go wrong. Then the same file through Spidra Parse in Python. We ran everything with pandas 2.2.3, openpyxl 3.1.5 and python-calamine 0.9.0.

The workbook: a tidy table, a report and a hidden sheet

Our test workbook has three sheets, built to look like something an office would actually send you. Orders is a tidy table of 40 orders with a totals row straight under the data. Summary is a small report: a merged title, a sentence of notes in merged cells, and two separate tables. Lookup is a hidden sheet of four SKUs.

Here are the first rows of Orders as a spreadsheet shows them:

The green corners in column E are the spreadsheet's own warning that those cells hold numbers stored as text. Column F holds the same SKUs as real numbers, and a number format makes them show their leading zeros. Keep both columns in mind. What we want from this workbook is typed records from Orders, and from Summary both the numbers and the sentence about the price correction.

Pandas guesses the types, and the totals row spoils them

The obvious first attempt is a single call:

import pandas as pd

orders = pd.read_excel("orders.xlsx", sheet_name="Orders", engine="openpyxl")
print(orders.dtypes.to_string())
print(orders.tail(2).to_string())

Output:

order_id              object
order_date    datetime64[ns]
customer              object
region                object
sku_text             float64
sku_num              float64
qty                  float64
unit_price           float64
discount             float64
total                float64
paid                 float64
notes                 object
       order_id order_date    customer region  sku_text  sku_num   qty  unit_price  discount     total  paid notes
39           40 2026-09-05  Brightside  South      88.0     88.0  26.0      165.69      0.15   3661.75   0.0   NaN
40  Grand total        NaT         NaN    NaN       NaN      NaN   NaN         NaN       NaN  58325.98   NaN   NaN

Three things went wrong, and pandas didn't say a word. The "Grand total" row made order_id an object column, because it now holds text. Every integer column turned into a float, because a totals row leaves blanks; blanks become NaN, and integers can't hold NaN: qty is 26.0, and paid is 0.0 where it should be False. And sku_text, which holds the text 00417, came back as the number 417.0. Pandas converts text that looks like a number, so the leading zeros are gone.

Tell pandas what the sheet is

Two arguments fix all three problems. skipfooter=1 drops the totals row, and dtype pins the one column that has to stay text:

import json

orders = pd.read_excel(
    "orders.xlsx", sheet_name="Orders", engine="openpyxl",
    skipfooter=1, dtype={"sku_text": str},
)
print(orders.dtypes.to_string())
records = orders.astype(object).where(orders.notna(), None).to_dict("records")
print(json.dumps(records[0], default=str))

Output:

order_id               int64
order_date    datetime64[ns]
customer              object
region                object
sku_text              object
sku_num                int64
qty                    int64
unit_price           float64
discount             float64
total                float64
paid                    bool
notes                 object
{"order_id": 1, "order_date": "2026-09-03 00:00:00", "customer": "Brightside", "region": "North", "sku_text": "00417", "sku_num": 417, "qty": 11, "unit_price": 237.23, "discount": 0.15, "total": 2218.1, "paid": true, "notes": null}

Now the columns have the types a person would expect: integers, a real date, a real boolean, and 00417 intact. The where(..., None) line is for the last step. Blank cells arrive as NaN, which isn't valid JSON. With the line, notes comes out as null, and without it you get nan. The read took 4 milliseconds.

Two notes on versions. We name the engine because the in-development pandas docs say the default for .xlsx files will change from openpyxl to calamine in a future version (we checked them on GitHub in October 2026), and naming it keeps your results the same after an upgrade. And pandas 3.0 changes how types are displayed: its release notes say a dedicated string type is now the default, so on 3.0 the text columns show as str and the dates as datetime64[us]. We checked the same fixed read on pandas 3.0.2 and it returns the same values.

Pull a table out of a report-style sheet

Summary is where a plain read falls apart:

plain = pd.read_excel("orders.xlsx", sheet_name="Summary", engine="openpyxl")
print(plain.head(6).to_string())

Output:

                                          Q3 Orders: regional summary Unnamed: 1 Unnamed: 2 Unnamed: 3
0  Prepared for the finance review. Figures exclude cancelled orders.        NaN        NaN        NaN
1                                                                 NaN        NaN        NaN        NaN
2                                                              Region     Orders    Revenue      Share
3                                                               North         10   23134.48   0.396641
4                                                               South          5    6714.22   0.115115
5                                                                East         13   14198.65   0.243436

pandas took the merged title as the column header, and the real header is three rows down. You tell it where the table is:

regions = pd.read_excel(
    "orders.xlsx", sheet_name="Summary", engine="openpyxl",
    header=3, nrows=4, usecols="A:D",
)
print(regions.to_string())
print(regions.dtypes.to_string())

Output:

  Region  Orders   Revenue     Share
0  North      10  23134.48  0.396641
1  South       5   6714.22  0.115115
2   East      13  14198.65  0.243436
3   West      12  14278.63  0.244807
Region      object
Orders       int64
Revenue    float64
Share      float64

Typed, clean, and exact. The second table on the sheet is the same call with header=15, nrows=5, usecols="A:B". The catch is that those numbers belong to this layout. If someone inserts a row above the table, header=3 quietly reads the wrong row.

Pandas also can't see some things on this sheet, and openpyxl can:

import openpyxl

book = openpyxl.load_workbook("orders.xlsx")
summary, orders_ws = book["Summary"], book["Orders"]
print([str(r) for r in summary.merged_cells.ranges])
print(summary["A12"].value)
print({ws.title: ws.sheet_state for ws in book.worksheets})
for cell in ("B2", "F2", "H2", "I2", "J2"):
    print(cell, repr(orders_ws[cell].value), orders_ws[cell].number_format)

Output:

['A1:E1', 'A12:D13']
Two orders were re-issued after a price correction; see the Orders sheet.
{'Orders': 'visible', 'Summary': 'visible', 'Lookup': 'hidden'}
B2 datetime.datetime(2026, 9, 3, 0, 0) yyyy-mm-dd
F2 417 00000
H2 237.23 $#,##0.00
I2 0.15 0%
J2 '=G2*H2*(1-I2)' $#,##0.00

That's the sentence about the price correction, sitting in merged cell A12, which a plain pandas read buries. It's also the number formats: the stored values are 0.15, 237.23 and 417, and the formats are what turn them into 15%, $237.23 and 00417 on screen. And pandas does read the hidden Lookup sheet when you pass sheet_name=None.

A formula is only a value if someone saved it

Column J of Orders holds formulas, and pandas gave us numbers for them. That worked because the file stores a value next to each formula. Formulas don't always have one. We built two small workbooks to show the cases:

for name in ("formulas_xlsxwriter.xlsx", "formulas_openpyxl.xlsx"):
    print("---", name)
    print("engine=openpyxl:")
    print(pd.read_excel(name, engine="openpyxl").iloc[:, 2:].to_string())

Output:

--- formulas_xlsxwriter.xlsx
engine=openpyxl:
   formula_with_saved_value  formula_default_value
0                        10                      0
1                        20                      0
2                        30                      0
3                        40                      0
4                        50                      0
--- formulas_openpyxl.xlsx
engine=openpyxl:
   formula_no_saved_value
0                     NaN
1                     NaN
2                     NaN
3                     NaN
4                     NaN

Three cases, one rule. When the saved value is right, you get it. When the library that wrote the file never computed anything, you get either a wrong value or no value. XlsxWriter saves 0 unless you give it a value (its docs say the default is 0), so formula_default_value is silently wrong: every row says 0. And openpyxl saves the formula and no value, so you get NaN. The calamine engine returned exactly the same two results. The load_workbook documentation in openpyxl says that reading a formula cell gives either the formula or "the value stored the last time Excel read the sheet". In our tests, nothing recalculated anything. We didn't test a workbook saved by Excel itself, which should store real values, so check yours.

That's the normal way, and for most spreadsheets it's all you need. Now the same workbook through Parse.

Send the same workbook to Parse

Parse turns a document into Markdown. A workbook is one of the formats it takes. We upload once with a free estimate, which also tells us the price, then parse by the file ID:

import os
import time
from spidra import Spidra

client = Spidra(api_key=os.environ["SPIDRA_API_KEY"])
est = client.estimate_parse("orders.xlsx")  # free
started = time.perf_counter()
doc = client.parse(file_id=est.file_id)
markdown = doc.markdown
print(doc.status, doc.format, f"{est.credits} credits estimated, {doc.credits_used} charged")
wall = time.perf_counter() - started
print(f"{doc.duration_ms} ms in Parse, {wall:.1f} s wall, {len(markdown):,} characters")

Output:

completed Excel 2 credits estimated, 2 charged
166 ms in Parse, 1.2 s wall, 4,982 characters

And this is what came back, trimmed:

## Orders

| order_id | order_date | customer | region | sku_text | sku_num | qty | unit_price | discount | total | paid | notes |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | 2026-09-03 | Brightside | North | 00417 | 00417 | 11 | $237.23 | 15% | $2,218.10 | TRUE |  |
| 2 | 2026-09-03 | Atlas Office | West | 00417 | 00417 | 12 | $147.78 | 5% | $1,684.69 | TRUE |  |
...
| Grand total |  |  |  |  |  |  |  |  | $58,325.98 |  |  |

## Summary

|  |  |  |  |  |
| --- | --- | --- | --- | --- |
| Q3 Orders: regional summary |  |  |  |  |
| Prepared for the finance review. Figures exclude cancelled orders. |  |  |  |  |
|  |  |  |  |  |
| Region | Orders | Revenue | Share |  |
| North | 10 | $23,134.48 | 40% |  |
| South | 5 | $6,714.22 | 12% |  |
| East | 13 | $14,198.65 | 24% |  |
| West | 12 | $14,278.63 | 24% |  |
...
| Notes |  |  |  |  |
| Two orders were re-issued after a price correction; see the Orders sheet. |  |  |  |  |
...

It's a document: each sheet is a heading, each table is a Markdown table, and the sentence about the price correction is right there in reading order, where you didn't have to know to look for it. Parse counted this as 2 pages for 2 credits. For files that aren't PDFs, the docs say it charges one page per 3,000 characters of output.

Parse returns the sheet as you see it, not as it's stored

Look at row 1 again. Where pandas returned 237.23, Parse returned $237.23. This is the main difference between the two outputs, and it follows from what each reads. pandas reads what the file stores. Parse reads what the sheet shows.

Sometimes that's what you want, and sometimes it isn't. The last row is the cost: the sheet shows whole percentages, so Parse's Markdown holds 40%, not the 0.396641 underneath. A few more things we saw in the output:

• Sheet names became headings in the one multi-sheet workbook we parsed. In the single-sheet workbooks, the Markdown was a bare table with no heading.
• The hidden sheet is missing. Lookup, with its Toner, Paper, Staples and Binder, doesn't appear anywhere in the output, where pandas read it.
• Blank rows and columns came through. The Summary table starts with an empty header row, contains six empty rows, and has a fifth, empty column, as wide as the merged title (A1:E1). They cost characters, and characters are what Parse charges for.
• Formulas became their saved values. The formula text isn't in the Markdown. Neither is a recalculated value, as the formula workbooks below show.

Because the output is text, you can search it without knowing where anything lives:

i = markdown.find("price correction")
print(markdown[max(0, i - 80) : i + 120])

Output:

|  |
|  |  |  |  |  |
| Notes |  |  |  |  |
| Two orders were re-issued after a price correction; see the Orders sheet. |  |  |  |  |
|  |  |  |  |  |
|  |  |  |  |  |
|  |  |  |  |  |
| Customer | Or

The formula workbooks behave the same way as pandas did, with one difference in how the failure looks. The workbook with saved values came back as 10 20 30 40 50, and the default-zero column as 0 0 0 0 0. The workbook with no saved values came back with empty cells, where pandas had NaN. Parse didn't recalculate anything either.

Ask Parse for fields from the messy sheet

There's one more thing Parse can do with Summary: skip the offsets entirely. You can describe the fields you want, and Parse finds them wherever they sit. This is AI extraction, so it adds token credits on top of the pages, and it's worth checking. The code reuses client and est from the step above.

REGION = {
    "type": "object",
    "required": ["region", "orders", "revenue", "share"],
    "properties": {
        "region": {"type": "string"},
        "orders": {"type": "integer"},
        "revenue": {"type": "number"},
        "share": {"type": "number"},
    },
}

import json

SCHEMA = {
    "type": "object",
    "required": ["regions", "note"],
    "properties": {"regions": {"type": "array", "items": REGION}, "note": {"type": "string"}},
}
PROMPT = (
    "From the Summary sheet, extract the regional summary table (region, orders, revenue, "
    "and share as a number between 0 and 1) and the text of the notes."
)
doc = client.parse(file_id=est.file_id, prompt=PROMPT, schema=SCHEMA)
print(json.dumps(doc.json, indent=2))

{
  "regions": [
    {
      "region": "North",
      "orders": 10,
      "revenue": 23134.48,
      "share": 0.4
    },
    {
      "region": "South",
      "orders": 5,
      "revenue": 6714.22,
      "share": 0.12
    },
    {
      "region": "East",
      "orders": 13,
      "revenue": 14198.65,
      "share": 0.24
    },
    {
      "region": "West",
      "orders": 12,
      "revenue": 14278.63,
      "share": 0.24
    }
  ],
  "note": "Two orders were re-issued after a price correction; see the Orders sheet."
}

It found the table and the note without being told where they were, and it returned the note word for word. (We didn't ask for the customer table.) We checked every number against the values pandas read from the file:

Orders and revenue matched exactly. The shares didn't, for the reason in the previous section: the sheet shows 40%, and that's what Parse read. 0.4 against a stored 0.3966 is a difference of 0.34 percentage points, and the largest gap was 0.49. If you multiply a share by a total, that rounding comes along with it. The run took 1.6 seconds in Parse and charged 3 credits, the 2 for the pages plus 1 for the AI tokens.

Markdown isn't a typed table

You can turn Parse's Markdown back into a DataFrame, and doing it shows what's missing:

import re

import pandas as pd

orders_md = markdown.split("## Summary")[0]
rows = [
    [cell.strip() for cell in line.strip().strip("|").split("|")]
    for line in orders_md.splitlines()
    if line.startswith("|") and not re.fullmatch(r"[|\s:-]+", line)
]
frame = pd.DataFrame(rows[1:], columns=rows[0])
print(frame.dtypes.to_string())
first = frame.head(3)
print(first["unit_price"].tolist(), first["discount"].tolist(), first["paid"].tolist())

Output:

order_id      object
order_date    object
customer      object
region        object
sku_text      object
sku_num       object
qty           object
unit_price    object
discount      object
total         object
paid          object
notes         object
['$237.23', '$147.78', '$63.96'] ['15%', '5%', '15%'] ['TRUE', 'TRUE', 'TRUE']

Every column is text, and the display formatting is part of the values: you can't add up $237.23 until you strip the dollar sign, 15% isn't 0.15, and TRUE is a string. You can clean all of that up, and then you've rebuilt what read_excel gave you in one line, with the rounding of whatever the sheet displayed baked in. That's the reason to keep records out of Markdown.

Typed values on one side, displayed text on the other

Time and cost scale differently

Parse was quick on every file we gave it. The cost is what moves with size:

Between 40 rows and 1,000, the time Parse spent hardly changed (166 and 151 ms), but the credits went from 2 to 34, because the Markdown went from about 5,000 characters to about 100,000. That's about 100 characters a row, or 0.034 credits a row. At that rate, 100,000 rows would be roughly 10 million characters, or about 3,350 pages, and the docs cap a document at 2,000 pages.

We can't tell you whether that cap is why the 100,000-row estimate failed. The error was a 422, "This document couldn't be read. It may be damaged," and the file isn't damaged: pandas read the same file in 4 seconds with openpyxl and under 1 with calamine. What it does tell you is to run the free estimate before you parse a big workbook, and to expect a large data sheet to cost a lot. The whole test, including the schema run, charged 41 credits.

Which one belongs in your pipeline

Pick by what you'll do with the output, not by which tool is newer.

The two aren't rivals. For a workbook like ours, one reasonable pipeline reads Orders with pandas, because you want typed rows, and sends the whole file to Parse when you also want the notes around the numbers to be searchable. For ordinary data analysis, Parse isn't a replacement for a spreadsheet library, and nothing we saw says it should be.