Extracting Cell Comments from Excel .xls Files: A Step-by-Step Guide

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

The Challenge of Accessing Cell Comments in .xls Files

When working with older Excel files (with a .xls extension), extracting cell comments can be challenging. Unlike the newer .xlsx format, which stores data in XML and makes it relatively straightforward to access comments, the binary file format (.xls) used by older versions of Excel requires more effort.

In this article, we’ll tackle how to extract comments from cells in .xls files using a combination of tools and manual methods. This is particularly useful if you need to preserve data integrity when migrating old Excel files or performing audits on legacy datasets.

Why Extracting Comments from .xls Files Is Tricky

The main issue lies in the binary file format used by .xls files, which doesn’t lend itself easily to direct inspection or manipulation. While you can view comments using tools like BIFFVIEW, programmatically accessing these comments requires diving into the binary structure of the Excel file.

How Comments Are Stored

Comments in older Excel files are stored as part of a complex record structure that includes:

• NOTE records: These contain references to where comments are located.
• OBJ records: These include the actual comment data.
• CONTINUE records: These extend OBJ records when they’re too large to fit in a single record.

Unfortunately, not all tools that read .xls files can handle these binary structures correctly. This is why you might find missing or incomplete data when trying to extract comments using standard methods.

A Step-by-Step Guide to Extracting Cell Comments

Step 1: Preparing Your Environment

Before we dive into the extraction process, make sure you have:

• The .xls file from which you want to extract comments.
• A tool like Desaware’s Storage Tools or similar for inspecting binary files.
• (Optional) A programming language like Python with libraries for handling Excel files (e.g., openpyxl, xlrd).

Step 2: Using BIFFVIEW to Inspect Comments

BIFFVIEW is a powerful tool that allows you to inspect the binary structure of .xls files. Here’s how:

1. Open your .xls file in BIFFVIEW.
2. Locate the NOTE records, which reference the comment locations.
3. Trace these references to their corresponding OBJ and CONTINUE records.

Step 3: Using Desaware’s Storage Tools

If BIFFVIEW alone isn’t sufficient, you can use Desaware’s Storage Tools to read the .xls compound file directly:

1. Open your .xls file in Desaware’s Storage Tools.
2. Navigate through the records until you locate FORMULA and NOTE records.
3. If OBJ and CONTINUE records are missing, consider using a more robust tool like CelTools for this step.

Step 4: Extracting Comments Programmatically (Optional)

For advanced users comfortable with programming, you can write a script to extract comments. Here’s an example in Python:

import xlrd

def extract_comments(filename):
    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)

    for row in range(sheet.nrows):
        for col in range(sheet.ncols):
            cell = sheet.cell(row, col)
            if cell.comment:
                print(f"Comment at ({row}, {col}): {cell.comment}")

extract_comments("example.xls")

This script uses the xlrd library to open an .xls file and extract comments from each cell.

Advanced Variation: Automating with CelTools

For those who need a more robust solution, CelTools offers advanced features for handling Excel files. It can automate the extraction of comments and other complex data from .xls files with just a few clicks.

Common Mistakes to Avoid When Extracting Comments

The process of extracting cell comments is fraught with potential pitfalls:

• Ignoring Record Types: Not all records in an .xls file are relevant. Focusing on NOTE, OBJ, and CONTINUE records is crucial.
• Using Incompatible Tools: Some tools might not handle the binary structure of .xls files correctly, leading to incomplete data extraction.
• Skipping Manual Inspection: Even with automated scripts, manually inspecting a few records can help ensure accuracy.

A Technical Summary: Combining Manual and Automated Methods

Extracting cell comments from .xls files requires a blend of manual inspection using tools like BIFFVIEW or Desaware’s Storage Tools, along with the option to automate parts of the process using scripts or specialized software like CelTools.

By understanding how Excel stores comments in its binary format and employing both manual and automated methods, you can efficiently extract this valuable data. For advanced users needing a reliable solution for frequent tasks, tools like CelTools offer a significant advantage by automating much of the process with minimal setup.