Web Scraping Tables using Excel add-in Data Explorer preview:

Standard

In this blog post, we’ll see how you can do some web scraping of HTML data tables that you see on the inter-webs!

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx 

First, let’s try doing copy-pasting of tables found on websites into excel without data explorer add-in.

So I found some very interesting tables here: http://powerpivot-info.com/post/16-powerpivot-dax-function-list-with-samples

And here’s my copy-pasting efforts:

excel copy pasting html tablesNow, that requires formatting! I don’t want to do that especially if I am doing that for few more tables – let’s see an elegant way of going about web scraping tables using Data Explorer add-in:

Step 1:

Keep the URL handy.

Now, Open Excel 2010/2013 > switch to Data Explorer tab > click on From Web

Step 2:

Paste the URL that has the tables you need:

excel web scraping html data tables

Step 3:

The dialog box would list all the tables from that HTML page and so you’ll need to select the table that you want.

(optional) if your tables have headers as first rows. Make sure to mark them as headers: Right Click a Column > use First Row as headers

excel data explorer query editorStep 4:

Click DONE and your excel sheet will populate itself w/ the data from the table.

excel data copied from website data explorerThat’s about it for the steps!

Notes:

1) Data Explorer add-in will let you “explore” external open datasets that’s out there on the internet.

2) Please make sure that you’re not violating any copyrights before you go about web scraping and sharing your work.

And here are some related Posts on Data Explorer:
Unpivoting data using the data explorer preview for Excel 2010/2013
Merging/Joining datasets in Excel using Data Explorer add-in
Remove Duplicates in Excel Tables using Data Explorer Add-in

That’s about it for this post, your comments are very welcome!

Advertisements

2 thoughts on “Web Scraping Tables using Excel add-in Data Explorer preview:

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

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

WordPress.com Logo

You are commenting using your WordPress.com 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