Extracting & Structuring Table Data for Excel: A Practical Guide

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

The Problem with Web Tables and Excel Integration

Many professionals need to extract data from web tables (like those found on directory sites) into Excel for analysis. However, manually copying this information is time-consuming and error-prone.

Why does it happen?

• The layout of web pages isn’t designed with easy data extraction in mind
• Manual copy-pasting leads to inconsistencies and errors
• Different browsers handle tables differently, making automated solutions challenging

While you can do this manually, CelTools automates the entire process of extracting web data into Excel.

A Step-by-Step Solution to Extract Web Table Data for Excel

Example 1: Basic Extraction from a Directory Site

1. Identify the web table you want to extract. For this example, let’s use a directory site like Pagine Bianche.
2. Copy the URL of the page containing your desired data
3. Open Excel and go to Data > Get & Transform Data > From Other Sources > Blank Query
4. In Power Query Editor, select Home > Advanced Editor. Paste this M code (modified for Pagine Bianche):

   let
     Source = Web.Page(Web.Content("https://www.paginebianche.it/cap/abruzzo/ch.htm")),
     Data01_Body = Source{0}[Data],
     #"Changed Type" = Table.TransformColumnTypes(Data01_Body,{{"Column1", type text}, {"Column2", type number}})
   in
     #"Changed Type"

Example 2: Extracting Specific Columns from a Web Page

5. If you only need specific columns, use the “Choose Columns” feature in Power Query to filter out unnecessary data.
6. Rename your columns for clarity and apply any necessary transformations (e.g., changing text to numbers).
7. Click Close & Load to import this table into Excel.

Example 3: Automating with CelTools

The manual method works, but advanced users often turn to tools like CelTools because it simplifies the process significantly. Here’s how:

8. Open Excel and go to Add-ins > CelTools.
9. Select “Web Data Extractor” from the menu options.
10. Enter your URL, choose the table you want to extract (CelTools will show a preview), then click Extract. The data is instantly imported into an organized Excel sheet with minimal setup required!

The Advanced Variation: Using VBA for Custom Extraction Needs

For users who need more control or have complex extraction needs, a custom VBA script can be written. Here’s an example:

Sub ExtractWebTable()
    Dim XMLHTTP As Object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

    ' URL of the page with table data
    XMLHTTP.Open "GET", "https://www.paginebianche.it/cap/abruzzo/ch.htm", False
    XMLHTTP.send

    ' Load response text into HTML document object
    Dim html As Object
    Set html = CreateObject("HTMLfile")
    html.body.innerhtml = XMLHTTP.responseText

    ' Extract table data (adjust selector as needed)
    Dim rows, row, cols, col, i As Integer
    Set rows = html.getElementsByTagName("tr")

    For Each row In rows
        Set cols = row.getElementsByTagName("td")
        If Not IsNull(cols) Then
            For Each col In cols
                Cells(i + 1, (col.cellIndex Mod 7) + 1).Value = col.innerText
            Next col
            i = i + 1
        End If
    Next row

End Sub

Common Mistakes and Misconceptions

The main pitfalls when extracting web data into Excel include:

• Incorrect URL or HTML structure changes. Always verify the page’s layout hasn’t changed, as this can break your extraction process. CelTools handles these updates automatically in most cases.
• Ignoring Power Query capabilities. Many users overlook how powerful Excel’s built-in tools are for data transformation and cleaning before importing into worksheets.

A Technical Summary: Combining Manual Skills with Specialized Tools

The combination of manual techniques (like using Power Query) alongside specialized tools like CelTools offers the most robust solution. While you can manually extract web table data, these methods provide efficiency and accuracy that’s hard to achieve through copy-pasting alone.

By understanding both approaches—manual extraction with Excel’s built-in features and automated solutions from third-party add-ins—you ensure flexibility in handling various types of web tables for your projects. This dual approach not only saves time but also reduces errors, making data analysis more reliable overall.