Export Kindle highlights to a database

Exporting Kindle highlights to a database can be achieved in a few different ways depending on how you access your highlights and what kind of database you are targeting (e.g., SQL, Notion, Airtable, etc.). Here’s a streamlined, step-by-step guide to export Kindle highlights into a structured database.

---

Methods to Access Kindle Highlights

1. Amazon’s “Your Notes and Highlights” Page

• URL: https://read.amazon.com/notebook

• Sign in to view all highlights synced from Kindle eBooks purchased via Amazon.

2. Kindle Device/Local File

• If you read using a Kindle device, your highlights are stored in a file:

  • File: My Clippings.txt (found in the Kindle root directory)

  • Path (Windows/macOS): Usually inside the Kindle’s USB folder when connected

---

Exporting Highlights: Step-by-Step

Option 1: Export from Amazon Notebook to a Database

Step 1: Scrape or Copy Highlights

• Manually copy-paste, or use browser extensions like Readwise or tools like Clippings.io, Bookcision, or KindleMate.

• Some tools offer export to .csv, .json, or direct database sync.

Step 2: Format for Database

• Parse the data into fields:

  • Book Title

  • Author

  • Highlight Text

  • Location/Page

  • Note (if any)

  • Timestamp (optional)

Step 3: Import into a Database

• SQL: Use a script or tool to import .csv or .json into MySQL, SQLite, or PostgreSQL.

• Notion: Use the Notion API or tools like Notion2Sheets, Readwise, or Zapier.

• Airtable: Import .csv directly or use API integrations.

---

Option 2: Export from My Clippings.txt (Kindle Device)

Step 1: Parse the Clippings File

• The file contains all highlights and notes in this format:

  pgsql
  CopyEdit
  ==========
  Book Title (Author)
  - Highlight on Page XX | Location XX-XX | Added on XX
  Highlight Text
  

Step 2: Use a Script (Python Example)

python
CopyEdit
import re
import sqlite3

# Parse the My Clippings.txt file
def parse_clippings(file_path):
    with open(file_path, 'r', encoding='utf-8') as f:
        content = f.read().split("==========")
    
    highlights = []
    for clip in content:
        lines = [line.strip() for line in clip.strip().split('n') if line.strip()]
        if len(lines) >= 3:
            title_author = lines[0]
            metadata = lines[1]
            text = " ".join(lines[2:])
            highlights.append((title_author, metadata, text))
    return highlights

# Insert into SQLite
def export_to_sqlite(highlights, db_name="kindle_highlights.db"):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS highlights (
            id INTEGER PRIMARY KEY,
            title_author TEXT,
            metadata TEXT,
            text TEXT
        )
    ''')
    cursor.executemany('INSERT INTO highlights (title_author, metadata, text) VALUES (?, ?, ?)', highlights)
    conn.commit()
    conn.close()

# Example usage
file_path = 'My Clippings.txt'
highlights = parse_clippings(file_path)
export_to_sqlite(highlights)

---

Automation and Tools

Readwise

• Syncs Kindle highlights from Amazon.

• Exports to Notion, Evernote, Roam, Obsidian, and CSV.

• Great for automatic syncing and categorization.

Notion API

• Use Python or Zapier to push highlights to a Notion database:

python
CopyEdit
import requests

def add_to_notion(page_data, notion_api_key, database_id):
    url = f'https://api.notion.com/v1/pages'
    headers = {
        'Authorization': f'Bearer {notion_api_key}',
        'Notion-Version': '2022-06-28',
        'Content-Type': 'application/json'
    }
    data = {
        "parent": {"database_id": database_id},
        "properties": {
            "Book": {"title": [{"text": {"content": page_data["title"]}}]},
            "Highlight": {"rich_text": [{"text": {"content": page_data["highlight"]}}]},
        }
    }
    response = requests.post(url, headers=headers, json=data)
    return response.status_code

Zapier / Make.com (Integromat)

• Use web automation to sync Amazon Highlights (via Readwise) to Google Sheets, Notion, Airtable, etc.

---

Recommended Database Structure

This schema supports filtering, searching, and relational linking with tags or categories.

---

Summary of Key Tools

---

With these steps and tools, you can easily extract, organize, and archive your Kindle highlights into a structured database that supports personal knowledge management or content repurposing.