Web data import to 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's a way of achieving already available in standard.

Well, no...

The web data connector available in the list of standard connection doesn't do that: a simple data extraction from a webpage.

I did a bit of research and found on 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.

---

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>

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 the first link <a> of the <td> within a <tr>. It's also the first <td> within a <tr>. So you could write the the xpath query two ways: "//tr/td/a[1]" or "//tr/td/a[1]".

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:

---

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 your 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 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 ?