Exporting Book Highlights From Kobo EReaders

• Sun, Jan 28, 2024
• 5-minute read

When it comes to eReaders, Kobo devices are my favorite. I own Kobo Libra 2 and Elipsa 2E. My Kobo library is growing with a plethora of books. I take lots of notes and highlights during my reading sessions on these devices.

I’ve always wanted to transfer all my highlights from the device to my notes. While there are numerous apps available for this purpose, I found myself searching for a more cost-effective or free solution to export the highlights. Interestingly, Kobo eReaders utilize a hidden SQLite database to store all detailing every book, highlights you’ve ever made and lots of other interesting data.

In this post, I’ll show you how to use this database and export your highlights to a markdown file. I’m keeping these files in my personal GitHub repo for future reference.

Understanding Kobo Database

Kobo eReaders store a wealth of data in an SQLite database named KoboReader.sqlite. This database, hidden within the .kobo directory, contains details about the eBooks you’ve purchased, downloaded, and your interactions with them, such as annotations and highlights.

After you connect the device to your computer using USB cable, Kobo eReader will appear as one of the volumes. You can view the hidden folder and files as shown in below screenshot:

Navigate to the .kobo directory at the root of the mounted drive. Here, you’ll find the KoboReader.sqlite file.

Copy this file to your computer for safe, easy access. Do not modify or delete the file on the device!

Extracting Highlights

The key to extracting highlights lies in understanding the database schema. Highlights and annotations are stored in the Bookmark table. You can execute SQL queries to retrieve this data. For instance:

SELECT ContentID, Text, StartContainerPath
    FROM Bookmark
    WHERE Text IS NOT NULL AND Text != ''
    ORDER BY StartContainerPath;

This query fetches all your highlights, organizing them by the book and the order in which they appear.

Once you’ve queried your highlights, most SQLite browsers will allow you to export this data in various formats, such as CSV or JSON, facilitating further analysis or backup.

Automating the Process with Python

Let’s create a Python program that connects to an SQLite database and retrieves a list of books with highlights, you’ll need to perform the following steps:

1. Connect to the SQLite database.
2. Query the database to get the list of books with highlights.
3. For each book, create a markdown file and format the highlights in markdown quote syntax.

Here’s a simple Python script to achieve the described functionality:

import sqlite3
import os

# Path to the Kobo SQLite database
db_path = './KoboReader.sqlite'

# Directory where markdown files will be saved
output_dir = './kobo_highlights'


def connect_to_database(path):
    """Connect to the SQLite database and return the connection."""
    try:
        conn = sqlite3.connect(path)
        return conn
    except sqlite3.Error as e:
        print(f"Error connecting to database: {e}")
        return None


def get_books_with_highlights(conn):
    """Get a list of books that have highlights."""
    books = []
    try:
        cursor = conn.cursor()
        query = """
            SELECT DISTINCT content.ContentId, 
                            content.Title,
                            content.Attribution AS Author, 
                            content.DateLastRead, 
                            content.TimeSpentReading
            FROM Bookmark
                INNER JOIN content ON Bookmark.VolumeID = content.ContentID
                ORDER BY content.Title;
        """
        cursor.execute(query)
        books = cursor.fetchall()
    except sqlite3.Error as e:
        print(f"Error fetching books: {e}")
    return books


def get_highlights_for_book(conn, content_id):
    """Get all highlights for a given book."""
    highlights = []
    try:
        cursor = conn.cursor()
        query = """ 
            SELECT Bookmark.Text FROM Bookmark
               INNER JOIN content ON Bookmark.VolumeID = content.ContentID
               WHERE content.ContentID = ?
        """
        cursor.execute(query, (content_id,))
        highlights = cursor.fetchall()
    except sqlite3.Error as e:
        print(f"Error fetching highlights for book {content_id}: {e}")
    return highlights


def create_markdown_for_book(book, highlights):
    """Create a markdown file for a book and insert all highlights."""
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)

    title = book[1]
    author = book[2]

    filename = (f"{title}.md".replace('/', '-')
                .replace('\\', '-')
                .replace(' ', '_').lower())

    filepath = os.path.join(output_dir, filename)
    with open(filepath, 'w', encoding='utf-8') as md_file:
        md_file.write(f"# {book[1]}\n\n")
        md_file.write(f"## Author(s) -  {author}\n\n")
        for highlight in highlights:
            md_file.write(f"> {highlight[0]}\n\n")


def main():
    conn = connect_to_database(db_path)
    if conn:
        books = get_books_with_highlights(conn)
        for book in books:
            highlights = get_highlights_for_book(conn, book[0])
            create_markdown_for_book(book, highlights)
        conn.close()


if __name__ == "__main__":
    main()

This script does the following:

• Connects to the Kobo SQLite database.
• Retrieves all books that have highlights.
• For each book, it retrieves all highlights and creates a markdown file with the book’s title as the filename.
• In the markdown file, each highlight is formatted as a quote.

Here’s an output file stored in my personal GitHub Repository:

Ending

Accessing the hidden SQLite database on Kobo device opens up a new world of possibilities for managing digital reading experience. Whether you’re backing up your data, analyzing your reading habits, or simply curious about the inner workings of your eReader, the insights you gain from this endeavor are both empowering and enlightening.

Remember, while exploring the depths of your Kobo device can be incredibly rewarding, always proceed with caution to avoid unintended consequences.

Happy hacking!