How to Extract a Table from a Web Page

There are four dependable ways to extract a table from a web page:

Copy and paste for a one-off visible table.
Google Sheets with IMPORTHTML for a quick spreadsheet import.
Excel Power Query when you need previewing, cleaning, and repeatable refreshes.
Python pandas when the table belongs in a data workflow.

The right method depends on whether the page contains a regular HTML table, whether the content is rendered in the browser, and how repeatable your process must be. Always compare the extracted headers, row count, and representative values with the source page before using the data.

1. Choose the extraction method

Situation	Best first option	Why
One visible table, extracted once	Copy and paste	Fastest and requires no setup
Import into a Google spreadsheet	`IMPORTHTML`	One formula and automatic refresh behavior
Excel workbook with cleaning or refresh steps	Power Query	Preview, transform, and load detected tables
Analysis, automation, or multiple pages	pandas `read_html`	Returns DataFrames that can be inspected and processed in code
Page needs browser rendering before capture or inspection	Browser automation or ScreenshotNeo	Handles a rendered view before you inspect the result

2. Copy a visible table into a spreadsheet

Open the page and wait until the table is fully visible.
Drag across the table, including the header row.
Copy it with Ctrl+C on Windows/Linux or Cmd+C on macOS.
Paste into Excel, Google Sheets, or another spreadsheet.
Check that columns stayed aligned and that no rows were omitted.

Some pages use visual layouts made from <div> elements instead of a semantic HTML table. Copying may then produce text in the wrong columns. If the pasted result is not rectangular, use Power Query, inspect the page source, or use a data endpoint provided by the site.

For a Python workflow, pandas also documents parsing copied tabular clipboard content:

import pandas as pd

df = pd.read_clipboard()
print(df.head())

This works when the clipboard contains tabular text that the CSV parser can interpret.

3. Import a table with Google Sheets

Google Sheets provides IMPORTHTML(url, query, index). The query is either "table" or "list", and the index starts at 1. Table and list indices are counted separately. See the Google IMPORTHTML documentation.

Basic formula

=IMPORTHTML("https://example.com/page","table",1)

Create or open a Google Sheet.
Click the cell where the imported data should begin.
Replace the URL with the page URL.
Use table to import an HTML table or list to import an HTML list.
Try index 1, then increase it if the page contains several tables.

Finding the right index

If the first result is a navigation table or an unrelated data block, try the next index. A page with three HTML tables uses indices 1, 2, and 3 for those tables. Lists have their own numbering, so the second list is still "list",2 even if several tables appear before it.

=IMPORTHTML("https://example.com/page","table",2)
=IMPORTHTML("https://example.com/page","list",1)

Google Sheets limits to account for

The page must expose the content in a form that IMPORTHTML can import.
A table generated only after JavaScript runs may not be available to the importer.
Authentication, consent screens, bot checks, and rate limits can prevent retrieval.
Layouts that look like tables but are not HTML tables may import poorly or not at all.

After the formula loads, compare its headers and a few values with the page. Do not assume the first imported range is the intended dataset.

4. Import and transform a table with Excel Power Query

Excel’s web connector can preview tables detected on a page, then let you transform or load a selection. Microsoft describes this workflow in its web connector support article and the Power Query Web Connector documentation.

In Excel, open the Data tab.
Choose From Web.
Enter the page URL and continue.
In Navigator, inspect the detected tables and the page preview.
Select Transform Data to clean the result, or Load to place it in the workbook.

Useful transformations

In Power Query you can remove extra columns, promote the first row to headers, change data types, split columns, filter rows, and combine tables. Keep those transformations in the query so a future refresh repeats the same cleanup.

When no tidy table is detected

Microsoft documents an example-based feature that lets you provide sample values so Power Query can infer matching content. Use Get web page data by providing examples when the desired information is structured consistently but is not exposed as a conventional table.

Power Query Online

Microsoft distinguishes the Web Page connector from the Web API connector. Power Query Online’s Web Page connector requires an on-premises data gateway because it retrieves HTML through a browser control; the Web API connector does not use that control. Plan the gateway requirement before moving a desktop query into an online workflow.

5. Extract HTML tables with Python and pandas

pandas.read_html accepts a URL, HTML string, or file and returns a list of DataFrames, even when the page contains only one table. That list is deliberate: inspect it and select the table you actually need. See the pandas IO tools documentation for parser details and HTML-table gotchas.

Install dependencies

python -m pip install pandas lxml

Read every table from a URL

import pandas as pd

url = "https://example.com/page"
tables = pd.read_html(url)

print(f"Found {len(tables)} tables")
for index, table in enumerate(tables, start=1):
    print(f"\nTable {index}: {table.shape[0]} rows x {table.shape[1]} columns")
    print(table.head())

Select and save a table

import pandas as pd

url = "https://example.com/page"
tables = pd.read_html(url)

if not tables:
    raise RuntimeError("No HTML tables were found")

table = tables[0]
table.to_csv("extracted-table.csv", index=False)
table.to_excel("extracted-table.xlsx", index=False)
print(table)

Select by a matching column

import pandas as pd

wanted = "Price"
tables = pd.read_html("https://example.com/page")

matches = [df for df in tables if wanted in df.columns]
if len(matches) != 1:
    raise RuntimeError(f"Expected one table with a {wanted!r} column; found {len(matches)}")

df = matches[0]
print(df)

Parse HTML already in memory

import pandas as pd

html = """

  Name Score
  Ada 10

"""

tables = pd.read_html(html)
print(tables[0])

Name	Score
Ada	10

Python edge cases

Multiple tables: inspect the returned list instead of assuming index zero is correct.
Malformed markup: parser behavior can vary; consult pandas’ HTML parsing guidance and try a different parser dependency when appropriate.
Nested headers: pandas may create a MultiIndex. Flatten it only after confirming the intended header structure.
Numbers and dates: inspect inferred dtypes and normalize them explicitly before calculations.
JavaScript-rendered tables: a direct HTTP request may receive only an empty shell. Use a browser-rendered source, an official API, or a capture workflow that can load the page first.

6. Verify the extracted result

Verification prevents a clean-looking spreadsheet from silently containing the wrong data.

Headers: compare every column name and order.
Row count: compare the number of data rows, accounting for pagination or expandable rows.
Representative values: check the first, middle, and last visible records.
Formatting: confirm decimal separators, currency symbols, dates, percentages, and negative values.
Hidden content: determine whether filters, tabs, lazy loading, or collapsed rows exclude data.
Duplicates: check whether a repeated header, footer, or mobile version was included.

Save the source URL and extraction time with the output when the data will be reused. For repeatable jobs, record the selected table index or selection rule and fail loudly when the shape changes.

7. Troubleshoot common failures

Symptom	Likely cause	Fix
Google Sheets imports the wrong table	Wrong one-based index	Try another table index; remember table and list indices are separate.
`IMPORTHTML` returns an error or empty range	Content is not exposed as importable HTML, or access is blocked	Open the page manually, inspect its structure, and use Power Query, pandas, an official API, or a rendered capture workflow.
Power Query shows several candidates	The page contains navigation, layout, or unrelated tables	Use Navigator’s preview or Web View and inspect headers and sample values before loading.
Power Query finds no suitable table	The content is structured with repeated elements rather than a tidy table	Try example-based extraction and provide representative values.
pandas returns several DataFrames	The page contains multiple HTML tables	Print each shape and header, then select by index or a required column.
pandas finds no tables	The response is a JavaScript shell, malformed HTML, or not a table	Inspect the response, consult the site’s API options, or use a browser-rendered method.
Columns shift after copying	The visual layout is not a semantic HTML table	Use Power Query, inspect the DOM, or locate a downloadable/API representation.
Rows are missing	Pagination, lazy loading, filters, or collapsed sections	Load all pages or states, then compare the final row count with the source.
Access denied or CAPTCHA	The site requires a visitor session or blocks automated requests	Follow the site’s access rules and use an authorized API or browser workflow; do not bypass controls.

8. Or skip the browser setup

ScreenshotNeo is a website screenshot API and MCP server. It can load a page before capture, which is useful when you need a reliable visual record of a table or want an AI agent to inspect pages. The API accepts options for full-page capture, waiting for a selector or network idle, custom headers and cookies, blocking requests, and more.

See the ScreenshotNeo API documentation for the complete option list. This one-call example captures a page as a WebP image:

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

Cookie banners, newsletter popups, and chat widgets are removed before the shot. Bot checks, blank pages, failed loads, timeouts, and cache hits are not billed, and response headers report the page verdict and billing status. Its MCP server lets Claude, Cursor, and other MCP clients use take_screenshot, get_page_info, and capture_pdf. The free plan includes 1,000 screenshots a month with no card; paid plans start at $5 for 3,000 shots.

Create a free ScreenshotNeo account to try it with 1,000 screenshots per month and no card.

9. Performance, reliability, and cost notes

Performance

Copy and paste is fastest for a single visible table.
Google Sheets and Power Query add convenience but depend on remote page access and refresh behavior.
pandas is efficient for repeated processing after the HTML is available; network latency and page complexity still dominate retrieval time.
For many URLs, batch requests or a site’s data export/API are usually easier to operate than opening each page manually.

Reliability

Prefer an official data API or download when the site provides one.
Pin your table-selection rule and validate schema changes.
Use timeouts and retry policies appropriate to your workflow.
Keep a source URL, retrieval timestamp, and validation result with each extraction.

Cost

Manual copying has no software cost. Google Sheets and Excel use the access and licensing available to your account. Python has no pandas license fee, but your compute, network, and any hosted browser costs still apply. ScreenshotNeo charges only for clean shots; failed loads, bot checks, blank pages, timeouts, and cache hits are not billed.

10. FAQ

Can I extract a table without coding?

Yes. Copy the visible table into a spreadsheet, use Google Sheets IMPORTHTML, or use Excel’s From Web connector.

Why does pandas return a list instead of one DataFrame?

A page can contain multiple HTML tables, so read_html always returns a list for inspection.

What if the table appears only after I click a tab?

A direct importer may not see it. Load the required state in a browser workflow, look for an official endpoint, or use a rendered capture process.

How do I know the extracted table is complete?

Compare headers, row counts, and representative values with the source, and account for pagination, filters, lazy loading, and collapsed rows.