There is a lot of data presented in a table format on the web pages. However, it could be quite difficult when you try to store the data on local computers for later access. The problem would be that the data is embedded inside the HTML which is unavailable to download in a structured format like Excel or CSV.
Web scraping is the easiest way to obtain data on your local computer. In the following parts, you can learn 4 easy ways to scrape table data from a website and export it into an Excel, CSV file, or to your database.
Scrape Table Data Without Coding
Octoparse is an easy-to-use web scraping tool that could help extract data at scale without any coding. It is easy to get started with the auto-detecting function, just wait automatically and use drag and drop to make some customization. You can also build a workflow with advanced functions to get more accurate information you need from any website.
Octoparse also provides preset templates for popular websites so that you can extract data within clicks. Just browse the template you want and preview the data sample it provides, finally, run the data template to start scraping.
https://www.octoparse.com/template/yahoo-finance-scraper
Octoparse is widely used among online sellers, marketers, researchers, and data analysts. If you want to customize your scraper, follow the steps below to scrape data from a table in detail by using Octoparse advanced mode.
How to scrape a table from a website with Octoparse
Step 1: First, you need to download Octoparse and launch it, and also create an account for free. Click Advanced Mode to start a new project.
Step 2: Enter the target URL into the box and click the Start button to open the website in Octoparse built-in browser.
Step 3: Click the table data fields you want to scrape, and follow the Tips panel it gives to create pagination, loop, AJAX, and more to customize your workflow.
Step 4: Scrape a table with the below clicks.
a) Click on the first cell in the first row of the table
b) Click on the expansion icon from the “Action Tips” panel until the whole row is highlighted in green color (usually the tag should be TR)
c) Click on “Select all sub-elements” in the “Action Tips” panel, then “Extract data” and “Extract data in the loop”
Step 5: The loop for scraping the table is built into the workflow. Run it to extract data and download it in any file format you want.
And the most amazing part is, we don’t need to know anything about coding. That said, whether we are programmers or not, we can create our “crawler” to get the needed data all by ourselves. To obtain further knowledge of scraping data from a table or a form, please refer to the detailed guide on How to extract data from a table/form or 3 methods to export HTML table to Excel.
Google Sheets to Scrape Table Information
In Google Sheets, there is a great function, called Import Html which is able to scrape data from a table within an HTML page using a fixed expression, =ImportHtml (URL, “table”, num).
How to use Google Sheets to extract table data
Step 1: Open a new Google Sheet, and enter the expression into a blank. A brief introduction of the formula will show up.
Step 2: Enter the URL (example: https://en.wikipedia.org/wiki/Forbes%27_list_of_the_world%27s_highest-paid_athletes) and adjust the index field as needed.
With the above 2 steps, we can have the table scraped to Google Sheets within minutes. Apparently, Google Sheets is a great way to help us scrape tables to Google Sheets directly. However, there is an obvious limitation. That would be such a mundane task if we plan to scrape tables across multiple pages using Google Sheets. Consequently, you need a more efficient way to automate the process.
Scrape Table with R language (using rvest Package)
In this case, I also use this website, https://www.babynameguide.com/categoryafrican.asp?strCat=African as an example to present how to scrape tables with rvest.
Before starting writing the codes, we need to know some basic grammars about rvest package.
html_nodes() : Select a particular part in a certain document. We can choose to use CSS selectors, like html_nodes(doc, “table td”), or xpath selectors, html_nodes(doc, xpath = “//table//td”)
html_tag() : Extract the tag name. Some similar ones are html_text (), html_attr() and html_attrs()
html_table() : Parsing HTML tables and extracting them to R Framework.
Apart from the above, there are still some functions for simulating human’s browsing behaviors. For example, html_session(), jump_to(), follow_link(), back(), forward(), submit_form() and so on.
In this case, we need to use html_table() to achieve our goal, scraping data from a table.
Download R (https://cran.r-project.org/) first.
Steps on using R to scrape the table data
Step 1: Install rvest.
Step 2: Start writing codes with the below keypoints included.
Library(rvest) : Import the rvest package
Library(magrittr) : Import the magrittr package
URL: The target URL
Read HTML : Access the information from the target URL
List: Read the data from the table
Step 3: After having all the code written in the R penal, click “Enter” to run the script. Now we can have the table information right away.
Scrape Table from Website with Python
Python is a widely used high-level programming language for general-purpose programming and data scraping. As an interpreted language, Python has a design philosophy that emphasizes code readability, and a syntax that allows programmers to express concepts in fewer lines of code than might be used in languages such as C++ or Java. Using Python to scrape data from a table/form is a good method if you are a programmer or if you are good at coding.
It seems that it doesn’t take less effort in using a web scraping tool than in writing a few lines of code to extract table data. In fact, programming does have a steep learning curve which raises the threshold for people, in general, getting into the real power of web scraping. This situation makes people who don’t work in the tech industry harder to gain a competitive edge in leveraging web data.
Final Thoughts
Hope the above tutorials will help you have a general idea of scraping table data from a website. Choose the way most suitable for you no matter coding or no coding. And Octoparse will be the best choice if you have no idea about coding, or you want to save time on scraping data.
