How to export a Wikipedia table to Excel or CSV
Open the article in Chrome, click the NeatCells icon and click Download Excel. Footnote marks such as [1] are left out, so populations, scores and dates arrive as real numbers and dates. For a table that stays linked to the article, use Excel's Data › From Web instead.
1. Export with NeatCells
- Open the Wikipedia article with the table.
- Click the NeatCells icon. It starts with the table that looks most like the article's main data. To take another one, click Select on the page and then one row of the table you want.
- Check the columns. Links to other articles get their own URL column and flags or pictures an image column; remove any you don't need with ×.
- Click Download Excel or CSV.
Wikipedia writes numbers for readers: "1,428,627,663" with a footnote mark after it, "17.3%", or a date in words. NeatCells reads each column as a whole, so the sheet gets numbers, percentages and dates that Excel can sort and add up. If you sort the table on Wikipedia first by clicking a column heading, the sheet keeps that order.
2. Excel's From Web
- In Excel, open the Data tab and click From Web.
- Paste the article's address and click OK.
- The Navigator lists every table in the article. The infobox and the navigation boxes count as tables too, so the one you want may be "Table 2" or later. Check the preview, then click Load.
Wikipedia's tables are plain HTML, so From Web usually finds them, and Data › Refresh All reads the article again later. Footnote marks come along, though: "1,428,627,663[1]" stays text until the mark is gone. Click Transform Data instead of Load, select the column, and use Transform › Extract › Text Before Delimiter with "[" before changing its type to a number.
3. Google Sheets' IMPORTHTML
In an empty cell, type the article's address and the table's number:
=IMPORTHTML("https://en.wikipedia.org/wiki/Article_name", "table", 2)
The number counts the tables in the article from the top, infobox included, so try 1, 2, 3 until the right one appears. If Sheets shows an error, see what IMPORTHTML errors mean.
Reusing Wikipedia data
For your own analysis, nothing else is needed. If you publish the table or a chart made from it, credit the article: Wikipedia's text is shared under the Creative Commons Attribution-ShareAlike licence (CC BY-SA 4.0).
Questions
Why does Excel show [1] after some numbers?
That is a footnote mark from the article, and it makes Excel keep the number as text. NeatCells leaves footnote marks out. In Excel, =VALUE(LEFT(A2,FIND("[",A2&"[")-1)) removes one.
The article has several tables. Which one do I get?
NeatCells starts with the one that looks most like the article's main data. To export another, click Select on the page and then one row of the table you want.
Does it work on Wikipedia in other languages?
Yes. Numbers written the local way, such as 1.234,5 or 1 234,5, are read as numbers too.