Wikipedia is the easy case and the hard case at the same time.
Easy, because the tables are in the HTML the server sends. Every tool that fetches a URL can see them, so you are not fighting JavaScript the way you are on a dashboard or an admin panel.
Hard, because Wikipedia's tables are written by people, for people to read. They are full of merged cells, footnote markers, hidden text and units glued to numbers. All of that comes across with the data, and most of it only becomes obvious once the numbers are already in your sheet and refusing to add up.
In Excel for Windows: Data, Get Data, From Other Sources, From Web, paste the article URL, wait for the Navigator pane, pick the table you want, click Load.
The Navigator lists every table on the page, named Table 0, Table 1 and so on, which tells you nothing. Click each one and look at the preview until you find yours. Long articles have twenty or more, counting the infobox and the navigation boxes at the bottom.
The query stays attached to the sheet, so Data, Refresh All pulls the current version of the article later. For a page that changes — a league table, a list of tallest buildings, an election result — that is worth having.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)", "table", 2)The last argument is which table, counting from 1. There is no way to see the numbering in advance, so start at 1 and work up until the right one appears. Infoboxes usually take the first one or two.
import pandas as pd
tables = pd.read_html("https://en.wikipedia.org/wiki/List_of_countries_by_population")
print(len(tables))
tables[0].to_excel("out.xlsx", index=False)read_html needs lxml or html5lib installed. It also understands rowspan and colspan, which puts it ahead of copy and paste.
The method matters less than the cleanup. These five come up on almost every article.
[1], [2], [a], [citation needed] — footnote links sit inside the cell, so they arrive as part of the text. A population of 1,417,492,000[3] is a string, not a number.
Strip them with a regular expression before anything else. In Excel there is no regex in older versions, so use Find and Replace with wildcards: find [*], replace with nothing. In Sheets:
=REGEXREPLACE(A2, "\[[^\]]*\]", "")In pandas:
df["Population"] = df["Population"].astype(str).str.replace(r"\[[^\]]*\]", "", regex=True)Sortable columns often carry an invisible sort value so that "1,417,492,000" and "12 March 1987" sort correctly. Those are real elements with display: none on them, and some templates put a marker character in as well. A tool that reads the cell's text without checking whether it is visible hands you a long run of digits, or something like 7002123400000000000♠, glued to the front of the value you wanted.
If you see that, the fix is not cleanup, it is a different tool: use one that skips hidden elements. Power Query and read_html are usually fine here because they work from the markup and Wikipedia marks these spans clearly. Plain copy and paste is usually fine too, because the browser does not put invisible text on the clipboard. Naive text extraction is where it bites.
This is the big one on Wikipedia. Country lists merge a region across several rows. Sports tables merge a season across the competitions in it. Discography tables merge chart positions across territories.
A cell with rowspan="3" exists once in the markup but occupies three rows on screen. If your tool copies the markup literally, the two rows below it are short by one cell, and every column in those rows shifts one place to the left. Nothing errors. You get a sheet where some rows have the population under the "capital" header.
Check for it directly: pick a row far down the table, and compare three or four of its values against the article. If they are one column out, that is this.
What you want is for the merged value to be expanded — repeated down each row it covers, so every row has a value in every column. Power Query and read_html both do this. Copy and paste does not.
1,417,492,000, $25,462, 4.3%, 12.7 km2, (2023), c. 1400. Excel stores all of those as text, so SUM returns zero and sorting is alphabetical: "9" comes after "1,417,492,000".
Strip everything that is not a digit, a minus sign or a decimal point, then convert. Beware one trap: Wikipedia uses a non-breaking space (U+00A0) as a thousands separator in a lot of articles. It looks like a normal space and Find and Replace on a normal space will not touch it. In Excel:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), ""), ",", ""))A country cell is usually a flag image plus a link plus sometimes a native-language name on a second line. Images drop out, which is what you want, but line breaks inside a cell survive and turn one cell into two lines. If a column looks right but the row heights are wrong, that is it. =CLEAN(A2) removes the control characters; a line break specifically is CHAR(10).
For tables you intend to process rather than read, the wikitext is sometimes easier than the rendered HTML. Append ?action=raw to any article URL and you get the markup:
https://en.wikipedia.org/wiki/List_of_countries_by_population?action=rawTable rows start with |- and cells with |. It is a small parsing job, but the sort keys and the reference templates are visible as templates rather than as text you have to guess at, and rowspan is written out explicitly.
There is also a small web tool, wikitable2csv, that takes an article URL and gives you CSV per table. Worth knowing about for a one-off.
If you are reading the article anyway, going out to Excel to fetch the URL is a detour, and it does not help at all with the tables that are not on Wikipedia — the ones behind a login, or drawn by JavaScript.
That is what GridPick is for. Click the toolbar icon on the article, it lists the tables it can see, you pick one and copy it to the clipboard or save it as CSV. It expands rowspan and colspan into a proper rectangular grid, and you choose whether a merged value repeats down its rows or leaves blanks below the first. It skips hidden elements, so the sort keys do not come with it. The free version has no row limit.
It will not strip reference markers for you — that is an editorial decision, not a formatting one, and a [a] sometimes is the value. Do that afterwards with one of the expressions above.
GridPick does this for you. It reads real tables and div-based grids, expands merged cells, warns you when a grid is only half loaded, and exports to Excel, CSV, JSON or Markdown. The free version has no row limit; Pro is a one-time payment.