Using Google Docs to scrape Wikipedia tables

Google Docs has an awesome function called ImportHtml. You can use it to import HTML tables into a spread sheet. You can then export the data as a flat file to import the data into your favorite database.

For example, this expression in cell A1 of a new spread sheet:

=ImportHtml(ʺʺ, ʺtableʺ, 2)

Will fire up a scraper in the background to fill the cells around A1 with all the data from Wikipedia’s table of ISO 3166-2 country codes.

I exported the sheet as a CSV so you can see the results. The file contains 250 lines: the first line is the header, and the remaining lines are 249 records mapping country codes to their English names.

The tables rows containing non-ASCII characters are slightly mangled in the output. For example,  country AX is called “Aland Islands !Åland Islands” instead of just “Åland Islands”.

This seems to be because of the way Wikipedia hacks the table data to implement a sort order. The following HTML fragment renders the row for country AX:

<td><a href="/wiki/ISO_3166-2:AX" title="ISO 3166-2:AX">AX</a></td>
<td><span style="display:none">Aland Islands !</span><span><a href="/wiki/%C3%85land_Islands" title="Åland Islands">Åland Islands</a></span></td>

It seems that the real data is prepended with a copy stripped of dicritic marks. The copy is prepended to an exclamation mark. Is it significant that “!” is the first non-whitespace printable ASCII character?

Anyway, the hack is made invisible to users by CSS (style=”display:none”), but the Google Docs scraper appears to be ignorant of the CSS. It apparently just extracts the values from all the text nodes of the table data element. This is reasonable behvaior in a general-purpose table scraper.

So, the output of the ImportHtml function should go through some cleaning process before you can load the data into your base tables, but the function will definitely give you a head-start in building a data set with contributions from Wikipedia.

If I find the time, I’d like to ask someone at Wikipedia why they hack the table data like that.


One thought on “Using Google Docs to scrape Wikipedia tables

  1. Pingback: Loading a flat file into a table « Base Table

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s