Import or Capture Web Data for Your Spreadsheet with Time-Saving Tools
Google famously wants to organize all the world’s information—and there’s a lot out there. You could spend many hours exploring just Wikipedia, which is a treasure trove of facts, figures, and miscellany. It’s no surprise when you come across a data table that piques your interest. In fact, there is a lot of open data trapped in HTML tables.
Often, you’ll want to take your research a step further and save the table to an ideal place for storing, manipulating, and analyzing data: a spreadsheet. But copy and pasting each row or even entire tables can be time-consuming, especially if there are multiple tables on the same topic you want to capture.
Instead, you can make better use of your time when you opt for tools that all but automate the task of grabbing data from the web and transferring it to Excel or Google Sheets.
In this post, we’ll highlight a few ways to capture data from the web. You can import external web data right in Excel, or use a browser extension to save time when grabbing web data. So let’s get started.
Get External Data to Excel: Web Import
Excel is the favorite spreadsheet tool of many for data visualization and analysis. But what if there’s data you want to manipulate that’s not yet in your spreadsheet? Whether it’s a table from an external source like the web or any other external source, the spreadsheet tool makes it easy to automatically import data into Excel.
While this method doesn’t work, when the option is available, it’s one of the easiest ways of importing into Excel.
- Navigate to the Data tab in Excel and click Get Data
- Select the data source you wish to import (From Web for this tutorial)
- Paste in the URL of your desired webpage and press the arrow near the specific table
you want to import - Click Import and identify a new Excel workbook or existing one
Other than pulling from the web, you can do the same in Excel From Access, From Text, From Other Sources, From SQL Server, or From XML Data Import—depending on your device. But for those without this Excel option, you can still copy a table to Excel or another spreadsheet, too.
Browser Extensions Make It Easy to Copy a Table from Website to Excel
No one wants to manually copy and paste rows and columns of data to a spreadsheet. Even if you can do so with an entire table, there might be several related tables you also want.
To copy one or more web tables, you’ll want a web extension like Table Capture or plug in to help you export HTML table to Excel. There’s often a free version of the most helpful browser extensions.
Free:
- Copy table data to the clipboard
- Export table to Google Sheets
- Edit table data before exporting
We find the above free options suitable for our data collecting needs, most commonly copying the table data to the clipboard and pasting it into our preferred spreadsheet tool: Google Sheets vs Excel: 3 Reasons Google Is the Supreme Spreadsheet Tool.
But the availability of the Pro version is useful for those with the following use cases:
- Download table as an Excel (.xlsx) file
- Download table as a CSV file
- Screenshot table (saves as a .png image)
- Export table to Office 365
- Copy to clipboard as Markdown
- Publish and share this table
In addition to the varying gathering methods, Table Capture Chrome enables you to Select all of the tables on a given page or Clear selection. Preview helps assure you’ve chosen the right table. Here are step-by-step directions on how to use it:
- Add the Table Capture Chrome extension
- Navigate the webpage you wish to pull a data table from
- Click the extension in the upper right-hand corner of your browser
- Identify your desired table
- Click the icon that represents the action you want to take
- Paste to your sheet if necessary
And there you have it! Once your data is in your spreadsheet, you’ll want to adequately prepare it for a map by removing links and other data clean-up tasks.
Gain Access to Data Blocked from Capture
For the occasional website that prevents you from easily grabbing their data with a browser extension that copies tables, there are, of course, workarounds.
But before resorting to an add-on like Enable Copy or Absolute Enable Right Click & Copy, be sure to check the site’s terms and conditions. Many have their reasons for not making their data accessible.
If you ultimately decide to bypass them, it’s always better to do so when you’re working on a project for your personal use rather than marketing or other commercial use.
Map Your Newly Gathered Data
No matter the source, we’ve shown how easy it is to grab any desired information with the help of different tools. Now the only thing left to do is map your data, going from this:
To an easier to visualize map:
View Mapped List of Captive Orcas in a full screen map
Of course, there are several ways to do so (we listed popular methods in our Introduction to Map Making on the Web. But perhaps the easiest way to map your data is with a web-based geocoder like ours. So let’s take a look at the next steps.
- Open your spreadsheet
- Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) your data
- Open your web browser and navigate to batchgeo.com
- Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
- Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
- Select the proper location column from each drop-down
- Click “Make Map” and watch as the geocoder performs its process
Get started mapping your data today with BatchGeo.
