Export all iPhone messages to Excel

Exporting All iPhone Messages to Excel#

I plan to find some messages to use as samples for training recognition.

At first, I planned to find junk message samples online, but I couldn't find any after searching for a long time. So I thought about using the messages on my own and my family's phones. After all, messages on phones are generally not deleted, and there are usually a few thousand of them, including junk messages, promotions, advertisements, and more.

So the question becomes, how do you export iPhone messages?

I searched for a long time and found that most of the third-party software I found requires payment. Finally, I found a free export solution.

First, back up your phone to your computer without encryption. As shown in the following image, select Back up all the data on your iPhone to this Mac and click Back Up Now. Wait for the backup to complete, and after the backup is complete, click Manage Backups.

Backup Interface

After clicking Manage Backups, the interface will appear as shown below. You can see the backup records. Right-click and select Show In Finder to open the folder.

Manage Backups

Then you can see that the backup directory has been opened. At this point, you need to find the file named 3d0d7e5fb2ce288813306e4d4636395e047a3d28. This file is the database file for message backup. Now the question is, how do you find it? Seeing all the folders in the backup directory might be confusing, but it's actually quite simple. Just search for it. Click the search icon in the upper right corner, and directly enter the file name. Note that the search scope is the current folder.

Backup Folder

The search results are as follows:

Message Backup Database File

Then copy this file to another location, such as the desktop, and open it with a database software like SQLPro for SQLLite. Open it as shown below:

Opening Message Database File

Upon observing this file, it is found that the phone numbers and message records are distributed in different tables. You need to write an SQL query to extract the desired content. The SQL query is as follows, referring to SQL to extract messages from backup. Select Query in the above image and enter the following command:

SELECT datetime(message.date, 'unixepoch', '+31 years', '-6 hours') as Timestamp, handle.id, message.text,
    case when message.is_from_me then 'From me' else 'To me' end as Sender
FROM message, handle WHERE message.handle_id = handle.ROWID AND message.text NOT NULL;

Then click Execute in the upper right corner. You can see that the messages have been filtered out.
Message Filtering

Then select all rows, right-click and select Export result set as, and export as CSV to export the file in Excel format.

Exporting Messages

This way, you can obtain the desired message samples.

References#

SQL to extract messages from backup