Scrape Table Data from a PDF into JSON

A statistical annex with footnote markers

Note the footnote marker stuck to a value ("4.1p" for provisional) and the source line that becomes a near-empty trailing row.

PDF:
Region        2025    2024
North         4.1p    3.9
South         5.2     5.0
Source: National Statistics, table 3a

Downloaded JSON:
[
  { "Region": "North",  "2025": "4.1p", "2024": "3.9" },
  { "Region": "South",  "2025": "5.2",  "2024": "5.0" },
  { "Region": "Source: National Statistics, table 3a", "2025": "", "2024": "" }
]

Clean: drop notes, strip markers, cast

Remove the source row, peel footnote letters/symbols off the figures, then convert to numbers.

const rows = JSON.parse(json);
const clean = rows
  .filter(r => r["2025"] !== "" && r.Region !== "Region")    // drop notes + repeated headers
  .map(r => ({
    region: r.Region,
    y2025: Number(String(r["2025"]).replace(/[^0-9.]/g, "")), // "4.1p" -> 4.1
    y2024: Number(String(r["2024"]).replace(/[^0-9.]/g, "")),
  }));

Forward-fill a merged category column

Where a category cell spans several rows, only the first row carries its text; the rest are blank. Carry the value down so every row is self-describing.

let last = "";
for (const r of clean) {
  if (r.category && r.category.trim()) last = r.category;
  else r.category = last;            // fill spanned cells
}

Load into pandas / Polars / DuckDB

Once it's a JSON array, every common analysis tool reads it directly.

# pandas
import pandas as pd
df = pd.read_json("clean.json")

# Polars
import polars as pl
df = pl.read_json("clean.json")

# DuckDB (no load step)
-- SELECT * FROM read_json_auto('clean.json');

Insert into Postgres with jsonb

For a quick landing table, stage the array as jsonb and expand columns in SQL.

CREATE TABLE staging (doc jsonb);
-- \copy staging(doc) FROM 'clean.json'  (or insert via your driver)

INSERT INTO regions (region, y2025, y2024)
SELECT e->>'region',
       (e->>'y2025')::numeric,
       (e->>'y2024')::numeric
FROM staging, jsonb_array_elements(doc) AS e;

Older government and archival PDFs are often scanned images. With no text layer, PDF.js finds nothing and the array is empty. Run PDF OCR first to add a searchable layer, then re-extract — and verify figures, since OCR errors on statistical data matter.

Long statistical tables reprint the column header on every page; from page 2 onward those become data rows. Filter where a key equals its own header text before loading.

Public tables flag provisional/revised figures with letters or symbols stuck to the number ("4.1p", "12.3*"). They extract verbatim. Strip non-numeric characters before casting, and keep the marker in a separate field if it's meaningful to your analysis.

The tool doesn't detect cell spans — a category that visually spans several rows only has text in its first row, leaving the rest blank for that column. Forward-fill the value down in your code so every row is self-describing (see the cookbook).

'Source:' lines, footnotes, and 'p = provisional' legends append as near-empty trailing rows. Filter rows where the data columns are empty to remove them cleanly.

The engine groups a whole page as one region, so two side-by-side or stacked tables merge into one block keyed by the first table's header. Split such pages first with PDF Split, or split the array on a boundary row afterward.

A permissions-locked PDF can block the text read. Remove the password with PDF Remove Password (you'll need it) or PDF Unlock first, then scrape. Respect the source's terms of use.

A long statistical release can top the free 50-page limit and be blocked before extraction. Upgrade to Pro (500 pages), or extract just the table pages with PDF Extract Pages first.

Very dense tables with tight columns can mis-assign cells by horizontal position, and long labels wrap to extra rows. Inspect the preview, merge wrapped rows, and remap drifted columns; for persistently messy sources, PDF to Excel gives a CSV you can realign in a spreadsheet.