Rosablanche

The problem: no simple way to import a web table into Tableau

So I needed to reuse data from a table in a web page to support a visualisation in Tableau and I thought surely there was already a way to achieve this out of the box.

Well, no…

The web data connector available in the list of standard connections doesn’t do that simple thing: extracting data from a webpage.

I did a bit of research and found an article on import.io that seemed to do just that. However, it didn’t work with Tableau Public or Tableau 2020.1. I tried to create a new account with import.io but was informed that:

As of August 19th, 2020 we have suspended the ability to create new Import.io accounts.

So, back to square one.

Now, Tableau can connect to Google Sheets or upload a file in Excel or csv format; it should also be possible to extract the data from a web table to the formats above and then process it in Tableau.

So let’s try to do just that.

The workaround: ImportXML in Google Sheets

I found this blog post:

How to Import Data from Any Web Page into Google Sheets with ImportXML (zapier.com)

It explains how to extract data from a web page with the importxml function in Google sheets.

I’m trying to extract the list of most popular websites and some metadata from Wikipedia (link here).

I’m interested in columns: site, domain, type, country.

Most useful info from the blog post above:

a tag with [1] means “only give me the first instance of <tag> inside <parent tag>.” So, td/span/a[1] gives you the first link inside the <span> inside each <td>. In the same way, td/b[1] gives you the very first bold text inside each <td>

Understanding the page’s XPath structure

So I try to replicate this logic with my table, but I need to understand the xml structure first.

In Chrome, right-mouse click in a table cell and select “Inspect” to view the code behind.

I click on the first cell of the table that contains “Google Search” and inspect it.

The syntax of the function is: IMPORTXML(url; xpath query). Both arguments are written between quotation marks ” “.

The xpath query is written: “//<parent tag>/<child tag>[column index]“.

Tags are written without the <>.

In this example, you see that the site name is the first link <a> of the <td> within a <tr>. It’s also the first <td> within a <tr>. So you could write the xpath query two ways: “//tr/td/a[1]” or “//tr/td[1]/a”.

Building the ImportXML formulas

To extract site, I type the formula below in cell A1:

=importxml("https://en.wikipedia.org/wiki/List_of_most_popular_websites";"//tr/td/a[1]")

The domain is the <td> number 2 within a <tr>. To extract domain, I type the formula below in cell A2:

=importxml("https://en.wikipedia.org/wiki/List_of_most_popular_websites";"//tr/td[2]")

The type is the <td> number 4 within a <tr>. To extract type, I type the formula below in cell A3:

=importxml("https://en.wikipedia.org/wiki/List_of_most_popular_websites";"//tr/td[4]")

And here is the result:

Importing the Google Sheet into Tableau

It is time now to import the data into Tableau.

Open up any version of Tableau and go to the Connect panel.

Click on Google sheets.

This will open a new tab in your browser where you’ll be asked to authenticate (you need to have a google account btw).

Once authenticated, a popup appears with the list of your g-sheets.

Select the one you’re interested in and click “Connect”.

Tableau displays the list of worksheet available in your g-sheet.

Double-click on the one you’re interested in and start exploring your data!

I’m not really satisfied with this process, it’s way too complicated, but that’s all I could find after a quick search. I will do more digging in the near future.

For comparison: how easy this is in Power BI

For the record, things are so much easier with PowerBI. 3 clicks and you’re done!

Here’s how:

In the “Get data” panel, select “Web”

Enter the website URL

PowerBI finds the table and identifies the columns

So much easier, no?