How to Scrape a Table From Any Website

The web’s most valuable data sits in tables, and the tutorials about getting it out are stale in two specific ways. I tested every route below this morning (October 16, 2026, home connection, Python 3.12). Wikipedia’s GDP list 403-blocks the default pandas setup, pandas 3.0 breaks the snippet most tutorials still print, and the table on DataTables’ own demo page is invisible until a browser renders it. The route that works when you scrape table from website markup depends on one question, and the first section shows you how to answer it in 30 seconds.

The 30-second check that picks your route

Open the page, press Ctrl+U (view source), and Ctrl+F for <table.

• Found it, and the rows are there. Static HTML table. Excel, Google Sheets, or pandas all work. Pick by how often you’ll repeat this.
• Found the <table> tag but no rows inside. The body loads with JavaScript. Excel and Sheets will show an empty result. You need a browser render (Step 4).
• No <table> tag at all, but the page looks like a grid. It’s a div-grid, common in dashboards and fintech sites. Standard tools return nothing. See Step 5.
• The table has pager buttons (Page 1, 2, 3…). Even a perfect render only gets you the visible page. Step 4 covers the workaround.

Five seconds of source-reading beats an hour of wondering why your spreadsheet is empty.

Route	Works on	Fails on	Cost
Excel Power Query	Static HTML tables	JS-loaded tables, bot blocks	Free, built into Excel
Google Sheets IMPORTHTML	Static HTML tables	JS tables, sites that block Google’s fetcher	Free
pandas read_html	Static tables, bulk work	UA blocks, the pandas 3.0 string change	Free
Point-and-click tools (Octoparse, ParseHub)	Static and JS tables, scheduled repeats	Paid tiers, account needed, same bot walls	Free tiers are page-capped
Browser-render scrape	JS tables; div-grids via AI extraction	Pagination needs the XHR (Step 4)	Free tool (LLM key only for AI extraction)

Step 1: Excel Power Query (no code)

Excel has shipped a web connector for years and it’s still the fastest path for a one-off.

1. Data → Get Data → From Web.
2. Paste the URL. Excel fetches the raw HTML.
3. The Navigator dialog lists every table it found, by index. Click through them; the preview shows content.
4. Click Load. The table lands in a worksheet, refreshable from Data → Refresh All.

What should you see: a proper spreadsheet with typed columns you can sort. The refresh button is the real feature: when the site updates its numbers, one click re-pulls.

Both failure modes are common on real sites, not edge cases:

• JavaScript tables return empty or missing rows, because Power Query fetches the same static HTML your view-source shows.
• Bot blocks return an error or an HTML error page instead of data. Wikipedia 403s any request whose User-Agent looks like a script, which is exactly what happened to pandas in Step 3. Sites behind Cloudflare behave worse. When your fetcher can’t identify itself as a browser, expect a block, not data.

When you hit either, go to Step 3 or Step 4. For the full Excel workflow including scheduled refreshes, see how to import website data into Excel.

Step 2: Google Sheets IMPORTHTML

One formula, no add-ons:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)", "table", 3)

The third argument is the table’s position in the page, counting from 1 in document order. Wrong index? You get a neighboring table or an #N/A. Nudge the number until the preview looks right.

IMPORTHTML re-fetches roughly every hour, which makes it the cheapest “living” copy of a table you can build. The same limits as Power Query apply, since it also parses static HTML and JS-loaded tables come back empty. Plus one more: some sites block Google’s fetcher outright, and you’ll see #N/A: Imported content is empty with no way to change the request. That’s your cue to move to pandas.

The Sheets-specific playbook lives in Google Sheets web scraping.

Step 3: pandas read_html (with the two fixes nobody mentions)

pandas.read_html remains the right tool for bulk work: every table on a page, as DataFrames, in one call. But the copy-paste snippet from older tutorials now fails twice before it works once.

Fix 1: the User-Agent. Pass the URL straight to read_html and Wikipedia returns HTTP Error 403: Forbidden, because the default Python User-Agent is on block lists. Fetch with a browser-like header instead.

Fix 2: pandas 3.0 wants StringIO. On pandas 3.0.0, handing read_html a string of HTML raises FileNotFoundError because it treats the string as a file path. Older tutorials write pd.read_html(html). Current pandas needs pd.read_html(io.StringIO(html)). I run pandas 3.0.0 with lxml 6.1.3; the working sequence:

import io, urllib.request
import pandas as pd

url = "https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)"
req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
html = urllib.request.urlopen(req, timeout=30).read().decode("utf-8", "ignore")

tables = pd.read_html(io.StringIO(html))
df = max(tables, key=len)        # pick the biggest table on the page
df.to_csv("gdp.csv", index=False)

What you should see, measured on that page: fetch 0.9s for 661 KB, parse 0.1s, and 8 DataFrames, because read_html grabs every <table> including navigation boxes. Six of the eight were navboxes and layout fragments with junk headers like vteLists of countries by GDP rankings; one more was a small 14-row regional-groupings table. The main one is 222 rows × 4 columns (Country/Territory, IMF 2026, World Bank 2025, UN 2024), which is why I pick max(tables, key=len) instead of a hardcoded index: it survives page edits that insert another navbox.

Inspect before you trust: df.head(), then df.dtypes. If numbers came in as strings, strip commas with df[col].str.replace(",", "").astype(float).

Need real Excel files rather than CSV? df.to_excel("gdp.xlsx"), after pip install openpyxl.

Step 4: JavaScript-loaded tables

Here’s the test case that kills Steps 1–3. DataTables’ own Ajax data demo renders a staff directory table. I pulled its HTML with curl: 2 <tr> tags, zero <tbody> content. The rows arrive via a separate XHR call after page load. Power Query, IMPORTHTML, and read_html all return an empty table against this page. Most table-scraping tutorials demo on Wikipedia and never mention this whole class of tables.

The fix is a browser render. With the magpie CLI (free download, 5 free runs per install, then $49/month):

magpie scrape "https://datatables.net/examples/data_sources/ajax.html" --render browser

Measured this morning: 6.4 seconds with a real Chromium doing the XHR, and the table comes back as clean markdown — 10 data rows starting with Airi Satou | Accountant | Tokyo | 5407 | 2008-11-28.

Ten rows, not 57: the demo paginates, and a render only sees page 1. Two ways past pagination:

• Find the XHR instead. Browser DevTools → Network tab → filter XHR, reload, and look for the request returning JSON. That endpoint is the real source; call it directly and skip the browser entirely. This is the reliable route and it’s the whole subject of scraping dynamic websites.
• Script the clicks. Browser-render scrapers can click “Next” in a loop and accumulate rows. Slower, and it breaks when the site moves the button, but it works when you can’t find the endpoint.

Step 5: when the grid isn’t a <table> at all

Dashboards built with React or Vue often render grids as nested <div>s with ARIA roles (role="grid", role="row"). No read_html call will ever see it. Your options, in order of preference:

1. The XHR endpoint, same as Step 4: div-grids also get their data from somewhere.
2. AI-assisted extraction. Magpie takes a JSON Schema and returns matching records as JSON, through any LLM provider you configure (Anthropic, OpenAI, Ollama for local models, and others), with a per-run cost cap. It’s the right tool when the “table” is visually obvious but structurally messy, and the wrong tool when a selector would do, since it costs tokens per run. Scraping a website with ChatGPT covers the manual version of the same idea.
3. BeautifulSoup and a grid walk. Find the row container class, iterate children, zip the cells. Twenty lines, brittle, but zero dependencies beyond bs4.

Where Magpie fits, honestly

I test it on every article because it’s this lab’s product, and tables are its rough edge. Simple tables inside normal articles come through cleanly: on MDN’s HTML tables tutorial, the sample tables arrived as proper markdown tables, pipes and all. But on Wikipedia’s GDP list, a complex sortable table with a three-row header, Magpie’s article-extraction pass flattened the whole thing into text soup: World128,003,776118,350,166111,565,144, zero markdown table rows in the output. Forcing --include table didn’t change that. When the table is the page’s main content and it’s structurally complex, pandas or the XHR beat it.

What Magpie did well in these tests: the browser render for JS tables (Step 4) worked, and its quality gate tells you plainly when a page refused scraping (“quality blocked: access-denied” on one test site) instead of writing a broken file. What it does not do, stated plainly: no .xlsx export and no Google Sheets connection. Output is markdown or JSON; you paste it into a worksheet or load it with pandas yourself. If your end goal is a spreadsheet, Steps 1–3 are shorter paths.

Keeping a table fresh

A table you scrape twice is a table you’ll automate. Three patterns that hold up:

• One URL, changing numbers: a scheduled read_html script, or Magpie’s watch mode, which re-scrapes on a schedule and fires a webhook when values change.
• Multiple pages, same site: crawl the section once with magpie map to list URLs, then loop.
• A site you don’t control: expect breakage. Magpie caches selectors per domain and re-synthesizes the ones that stop matching, which absorbs minor redesigns; it can’t absorb a rewrite. Re-run your 30-second source check whenever a fetch comes back empty. The general playbook is in how to scrape data from any website.

FAQ

Can Excel pull a table from a website automatically? Yes: Data → Get Data → From Web, then pick the table in Navigator. It refreshes on schedule via Data → Refresh All. It only parses static HTML, so JavaScript-loaded tables come back empty.

How do I scrape a table into Google Sheets? =IMPORTHTML("URL", "table", N) where N is the table’s position on the page. It re-fetches about hourly. Sites that block Google’s fetcher, and JS-loaded tables, return #N/A.

Why does read_html say “No tables found”? Either the page genuinely has no <table> markup (a div-grid, see Step 5), or the rows load with JavaScript, so the HTML you fetched is empty. View source and search <table to tell which.

Is scraping tables from websites legal? Public data behind no login is generally fine to read; courts have gone back and forth on edge cases. Respect robots.txt, rate-limit yourself, and never scrape personal data or anything behind authentication without permission.

How do I get all rows of a paginated table? Find the XHR endpoint that returns the data (DevTools → Network → XHR) and call it per page; it’s usually a plain JSON API. Clicking “Next” in a loop works too, but breaks more often.

Where “scrape table from website” goes wrong

Four failure patterns cover almost every case you’ll hit. Empty spreadsheet or missing rows: the table loads with JavaScript, go to Step 4. HTTP 403 or an error page: the site blocked your fetcher’s User-Agent, use the pandas fixes. No tables found in pandas: there is no <table> tag, it’s a div-grid, Step 5. Exactly 10 or 25 rows when the site shows more: pagination, and the render or import only grabbed page 1.

My default order: view source first, Excel or Sheets for a one-off with static rows, the pandas sequence saved as a script for repeat work, DevTools for the XHR before any browser render. And when a fetch comes back empty, don’t retry — open view source and see what the server actually sent you. That one habit fixes more “broken scrapers” than any tool on this page.