iMessage Attachments and Links Extractor for macOS

iMessageAttachmentsLinksExtractor.sh

#!/bin/bash

# Define the main export folder
export_folder=~/Desktop/iMessages_Export
mkdir -p "$export_folder"

# Part 1: Generate the CSV file
echo "Generating CSV file..."

sqlite3 ~/Library/Messages/chat.db <<EOF
.headers on
.mode csv
.output $export_folder/messages.csv
SELECT
    chat.chat_identifier AS contact,  -- Unique identifier for each conversation or group
    strftime('%Y%m%d', message.date/1000000000 + strftime('%s', '2001-01-01'), 'unixepoch') AS date,
    attachment.filename AS attachment_path,
    message.text AS message
FROM
    message
JOIN
    chat_message_join ON message.rowid = chat_message_join.message_id
JOIN
    chat ON chat_message_join.chat_id = chat.rowid
LEFT JOIN
    message_attachment_join ON message.rowid = message_attachment_join.message_id
LEFT JOIN
    attachment ON message_attachment_join.attachment_id = attachment.rowid
ORDER BY
    contact, date;
EOF

# Check if the CSV file was created successfully
if [ ! -f "$export_folder/messages.csv" ]; then
    echo "Error: messages.csv was not created. Check SQLite command syntax and permissions."
    exit 1
fi

echo "CSV file created at $export_folder/messages.csv."

# Part 2: Use the CSV file to organize files by conversation
echo "Organizing files and links by conversation..."

# Process each line in the CSV file (skip the header)
tail -n +2 "$export_folder/messages.csv" | while IFS=, read -r contact date attachment_path message; do
    # Extract phone numbers from `contact` using regular expression
    phone_numbers=$(echo "$contact" | grep -oE '\b[0-9]{10,}\b' | tr '\n' '-')
    phone_numbers=${phone_numbers%-}  # Remove trailing hyphen if it exists

    # If no phone numbers are found, fall back to "Unknown"
    if [ -z "$phone_numbers" ]; then
        convo_folder="$export_folder/Unknown"
        convo_id="Unknown"
    else
        convo_folder="$export_folder/$phone_numbers"
        convo_id="$phone_numbers"
    fi

    # Create the main folder for this contact if it doesn't already exist
    mkdir -p "$convo_folder"

    # Define the paths for subfolders with the conversation ID prepended
    files_folder="$convo_folder/${convo_id} - Files"
    links_folder="$convo_folder/${convo_id} - Links"
    mkdir -p "$files_folder"
    mkdir -p "$links_folder"

    # Organize links (URLs) into a single CSV file per conversation
    if [[ "$message" =~ (http|www\.) ]]; then
        # Define links CSV path with the conversation ID prepended
        links_csv="$links_folder/${convo_id} - links.csv"
        # Append to links.csv with format: "conversation_id,date,url"
        echo "$convo_id,$date,\"$message\"" >> "$links_csv"
    fi

    # Process attachments if available
    if [ -n "$attachment_path" ]; then
        # Truncate attachment filename to 20 characters for the base name + extension
        attachment_name="${attachment_path##*/}"
        base_name="${attachment_name%.*}"
        extension="${attachment_name##*.}"
        truncated_name="${base_name:0:20}.$extension"

        # Prepend the conversation ID to the filename
        final_filename="${convo_id} - $truncated_name"

        # Use find to locate the attachment within the Attachments directory
        attachment_full_path=$(find "$HOME/Library/Messages/Attachments" -type f -name "$attachment_name" 2>/dev/null | head -n 1)
        
        if [ -f "$attachment_full_path" ]; then
            cp "$attachment_full_path" "$files_folder/$final_filename"
        else
            echo "Warning: Attachment file not found for $attachment_name"
        fi
    fi
done

# Remove empty folders
find "$export_folder" -type d -empty -delete

echo "Files and links organized by conversation. Check $export_folder for the results."

iMessage Attachments and Links Extractor for macOS

Note: This script only extracts attachments (e.g., photos, videos, audio files) and URLs from iMessages. It does not extract entire conversations or text message content.

This script organizes extracted files and links into folders based on the contact or group conversation source from macOS's Messages app database (chat.db).

Overview

The script performs two main actions:

1. Generate a CSV File: Extracts data from the chat.db SQLite database, saving the information in messages.csv. This file includes details about each message, including the contact identifier, date, message content, and attachment path.

2. Organize Files and Links by Conversation: Reads messages.csv to create folders for each unique conversation (based on phone number or group ID), copying attachments and organizing URLs into a single links.csv file per conversation.

Key Features

• Folders Named by Contact: Each conversation is organized into a folder named after the phone number(s) associated with the contact or group. Group conversations use hyphen-separated phone numbers.
• Single Links File: Each conversation folder includes a single links.csv file listing all URLs sent by that contact, with the date and URL.
• Recursive Attachment Search: Given the nested structure of the macOS Attachments folder, the script uses a recursive search to locate attachments.
• Fallback Handling: For unidentified contacts, folders are labeled as "Unknown." Empty folders are automatically removed.

macOS Attachment Path Structure

In macOS, attachments are stored in ~/Library/Messages/Attachments/ with a two-level hexadecimal subdirectory structure, followed by UUID-based subfolders for individual attachments. This structure can make finding files challenging, especially if iCloud is managing message storage.

The script handles this by searching through all subdirectories under Attachments to locate the correct file.

Dependencies

• macOS: This script is tailored for macOS and its Messages app database structure.
• SQLite: Used to query chat.db and extract message and attachment data.
• find: The find command is used to search for attachment files in the complex directory structure of ~/Library/Messages/Attachments.

Script Usage

1. Save the Script as iMessageAttachmentsLinksExtractor.sh

Copy the script code above into a file named iMessageAttachmentsLinksExtractor.sh.

2. Make the Script Executable

Run the following command in Terminal to make the script executable:

chmod +x iMessageAttachmentsLinksExtractor.sh

3. Run the Script

Execute the script:

./iMessageAttachmentsLinksExtractor.sh

The script will create an iMessages_Export folder on your desktop, with organized folders by conversation ID. Each folder will contain:

• A Files directory with all found attachments.
• A Links/links.csv file with all URLs sent by that contact, including the date.

Troubleshooting

Attachment Files Not Found

If the script cannot locate some attachments, it may be due to:

1. macOS Offloading Attachments to iCloud: If iCloud is managing your Messages storage, some attachments might be stored remotely. To access these, download them via the Messages app.
2. Alternate Attachment Paths: macOS may store attachments in alternative directories depending on the OS version. This script searches recursively in ~/Library/Messages/Attachments, so it should locate files in most cases.

Efficiency Note

The script performs a find operation for each attachment, which may slow down the process if there are many attachments. This approach is necessary due to the complex folder structure of the Attachments directory.

Additional Notes

• macOS Compatibility: This script is designed for macOS and uses its standard Messages app database structure.
• SQLite: The script requires SQLite to access chat.db.
• File Cleanup: Empty folders are removed after organizing the files to keep the export folder clean.

---

This script offers a powerful way to organize iMessage data, making it easier to locate important files and links from specific conversations.