How to Parse Data in 5 Simple Steps

How often do you need to parse data from a website for your work, but it seems like too complex a task? This blog post will teach you how to collect data in 5 simple steps.

This article is written for software professionals who need to extract data from web pages or PDF files and want the process to be as fast and painless as possible.

There are many ways to parse data, but in this article we will focus on a few of the most popular methods used by professional developers: XPath, regular expressions, Beautiful Soup (BSA), Scrapy, Python Requests Library (PRL), and Selenium Webdriver.

All of these scraping tools are easy to install, and instructions are provided for each. Below is a table describing the pros and cons of these tools, along with links to further information where needed. The table includes some other, less popular methods that I have also tried, included for completeness.

Data collection from a website can be done with any programming language.

In this article, I will show how to parse data using Python, as it is very easy to install and fairly simple to learn, especially if you already know another programming language, since the syntax is similar to Java, C, and PHP. Python supports several libraries for data parsing, which are described in the table. Parsing is a tool everyone should know how to use, as it allows you to gain valuable information about your target market, competitors, and/or customers.

Site parsing can help you extract various types of data. By writing a few lines of code, you can find real estate listings, hotel data, or even product data with prices on e-commerce sites. But if you are going to scrape web pages and collect data, you need to take care of a few things.

It is important to ensure that the scraped data is in a usable format. Your code may extract data from multiple websites. The extracted data should be clean and not produce erroneous results when algorithms are run. So, if you want to write your code or create a small scraping project to collect data from sites, you can do it in five simple steps:

How to Parse Data in 5 Simple Ways

1. Choose a programming language or tool for data parsing

"A man is only as good as his tools." Therefore, you need to choose a site parsing tool that fits your needs. While some ready-to-use programs may seem simple, they may not allow you to make many configuration changes.

At the same time, many tools may lack a plugin to save the data you collect to a database or the cloud. When it comes to programming languages used for data parsing today, these include Node.js, Python, Ruby, and others. But among them, Python is the most popular due to its ease of learning, simple syntax, availability of many external libraries, and open source nature.

There are many libraries such as BeautifulSoup and Scrapy that can be used to crawl web pages, create crawling scripts, and run scraping jobs at intervals. Python provides tremendous flexibility when integrating other systems with your scraping engine. You can easily save the resulting data on your local machine, in databases, in S3 storage, or even dump it into a Word or Excel file.

2. Parse a single web page and analyze the components

Before you start parsing product information from, say, a thousand product pages on Wildberries, you need to access the page and retrieve the entire HTML page to analyze it and decide on a strategy. Data on HTML pages can be in specific key-value pairs in tags or in text within tags. You can use libraries like BeautifulSoup to specify exactly which tag you want to extract data from on each web page, then run the code in a loop.

Thus, for each individual product web page, your code will run and extract the same information—say, price data.

3. Decide on a data cleaning and storage strategy

Even before you start collecting data, you should decide where to store it. This is because where you store the data will affect its processing. There are many options. You can choose between NoSQL and SQL databases, depending on whether the data you collect will be structured or unstructured.

For structured data, you can choose SQL databases, as you can store data in rows consisting of a set of attributes. For unstructured data where there are no set attributes, you can use NoSQL databases. As for which database to store the data in, for SQL you can choose MySQL or Postgres. Selectel offers cloud databases where you can store your data and pay per usage.

For NoSQL, you can choose one of the fully managed and extremely fast solutions like DynamoDb or ElasticSearch. Different databases have their advantages; some offer fast search, some cheaper storage per TB.

The choice of database depends on your specific use case, and therefore. You need to do a little research before settling on one. If you need to store large images and videos, you can use AWS S3 or Glacier. The latter is used when you want to store large amounts of data in an archival format. You won't need to access them frequently, while the former is a more common solution. It's like an online hard drive. You can create folders and save files in them.

4. Create a list of web pages or write a regex for data cleaning

While you can test your code on a single web page, you likely want to scrape dozens or hundreds of pages, which is why you started this project. Typically, if you are going to iterate over multiple web pages, you can store the links in an array and iterate over them as you scrape. A better and more commonly used solution is to use regex. Simply put, it's a programmatic way to identify websites with similar URL structure.

For example, you might want to get product data for all laptops on Ozon. Now you can see that all URLs start with "/laptop/<laptopModelNo>/prodData". You can repeat this pattern with regex to extract all such URLs and have your parsing function work only on those URLs. Not on all URLs on the site.

If you have too many web pages, we recommend using parallel processing to scrape about ten web pages at any given time. If you are scraping web pages and extracting links from them, then scraping the web pages those links point to, you can use a tree approach to simultaneously collect multiple child pages arising from a root web page.

5. Write the code and test

Everything we've discussed so far was preparation. And now the moment has come when the code runs and the work is done. Parsing code rarely works exactly as expected. This is because not all web pages you try to scrape have the same structure. For example, you run your script on 100 product pages and find that only 80 were saved.

The reason is that 20 pages may be in an "out of stock" state and their web page structure differs. Such exceptions are not accounted for when writing the code. But after a few iterations, you'll be able to make the necessary changes. And extract data from all the web pages you need.

Alternative Data Parsing Methods

Use Excel's filter function to select data

This is the simplest way to extract data from any web page. It requires no additional tools other than Excel, and leaves no trace on the target website or your system, i.e., no API calls are made, no cookies are set, etc. If you have access to a PC/Mac with Excel installed, this is by far the easiest way to collect data.

How it works

In Excel, open a new sheet and write the target site's URL in the first cell. In the next cell, enter the HTML or XPath data filter formula (see the table below for the XPath data filter formula used in this example), which will extract data from the target web page. If you want to save multiple data columns, repeat Step 1 for each column and place them side by side.

How to get XPath data filters in Excel

• In Excel, click File > Options > Advanced, then go to the Display section. Check the box "Show Developer tab on the ribbon" and click OK.
• In Excel, click Developer > Reference, then scroll down and find Microsoft XML Document 3.0.
• Click on MSXML3 Apress, then click OK.
• Now that we have the MSXML3 Reference installed, let's try it. In Excel, enter the target web page URL in cell A1, then enter the XPath Data Filter formula in cell B1 (e.g., to get the price of product "123" on a web page, enter the XPath Data Filter formula in cell B1)
• Now copy the contents of cell B1 to all remaining cells using the keyboard shortcut CTRL+C in Excel.
• Copy the target web page URL (in cell A1) into Excel's address bar. Press Enter to load it in the browser, and watch your data magically appear in Excel.

As you can see, this is an extremely efficient way to collect data. It leaves no trace on the target site or your system, i.e., no API calls are made, no cookies are set, etc.

Using Microsoft Word's "Text to Columns" function to split data into columns

This method is a bit more complex than the first, but can be used to map data from any site that has clearly defined columns. It requires MS Word and Excel, as well as some additional tools described below. The bad news is that this method leaves traces on the target site, so if you are parsing data from websites for marketing purposes and want to avoid detection, this method is not for you.

How it works

• In MS Word, create a new document, then copy/paste the target web page into a text box.
• Select the Goto function in MS Word by pressing CTRL + G, then type <<!DOCTYPE HTML [ENTER].
• Select the Run function in MS Word by pressing ALT + F5, and you should see something like the following.
• In Excel, select Filters > Text to Columns
• Select Delimited and choose the delimiter from the dropdown (in this example we use Comma, which is selected by default).
• Now click Next and define the location of "commas", i.e., split the web data into columns (e.g., I use "," as delimiter).
• Then click Next and define how Excel will handle empty cells: ignore them or replace with a blank value (for this example we will set to "ignore blank values").
• Then click Finish to complete the Text to Columns function.
• If your target web page does not have clearly defined columns, you can set the "Replace values" option.

As you can see, my data was successfully split into separate text columns, and now I can easily copy/paste it elsewhere on my computer.

This method is especially useful for extracting data from PDF files, which usually have clearly defined column headers. It will save you a lot of time if your goal is to automatically extract data from multiple PDF files using Excel macros, since you can now reuse the same MS Word template over and over to quickly create multiple documents for each PDF.

Use the MS Excel VBA editor to extract data

For this method, we will use a tool called xvba, which can be downloaded from the official site, and it works like an API (Application Program Interface). The good news is that you don't need to write any code. The bad news is that you'll have to enter the target web page's HTML, i.e., I'd suggest doing it with MS Word because it forces you to actually break down your web page into components.

The numbers in square brackets are individual cells in the spreadsheet where column and row headers were used as names for each data field. They act as variables, i.e., are treated by Excel as text and will change depending on how your spreadsheet is structured (e.g., if you select a different column for data filtering).

Using IMPORTXML in Google Sheets

If you use IMPORTXML to map data from a web page.

• The first step is to open Google Sheets.
• The second step is to go to the Tools menu > Script Editor.
• The third step is to copy/paste the target website's HTML code into the text field.

My data was successfully split into separate text columns, and now I can easily copy/paste it elsewhere on my computer.

In the past, we've used this method to collect Facebook and Twitter data for an online marketing campaign. It's also a good way to map data from websites using AJAX when you don't want to use the GOTO command.

Using IMPORTHTML in Google Sheets

This method is similar to the above, but instead of IMPORTXML, we will use IMPORTHTML.

• The first step is to open Google Sheets.
• The second step is to go to the Tools menu > Script Editor.
• The third step is to copy/paste the target site's HTML code into the text field.

Using Chrome extensions

You can parse web data with chrome extensions like Data Scraper and Save To CSV. Since there is a free and paid version of chrome extensions (Data Scraper), I would suggest using both versions to see which one suits you best.

Conclusion

Be sure to take a few seconds break between each run. Scraping a website once a month will work well with a DIY solution written in Python. But if you are looking for an enterprise-level DaaS solution, our team at ESK Solutions offers a comprehensive solution where you provide us with requirements, and we deliver the data, which you can connect and work with.

Infrastructure, proxy management, ensuring you don't get blocked while collecting data. Running the scraping engine on a schedule with regular frequency to update data. We also make changes according to changes made to the UI of the relevant site.

All of this is done as part of our fully managed solution. It is a cloud service with pay-per-use billing. It will satisfy all your site scraping needs. No matter if you are a startup, a large corporation, or you need data for research work. We have data parsing solutions for everyone. We also invite you for unique web development according to the client's technical specifications.