Export all iPhone messages to Excel.

Export All iPhone Messages to Excel#

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

At first, I planned to find spam 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 usually not deleted, and there are usually a few thousand of them, including spam messages, promotions, advertisements, and so on.

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

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

First, back up your phone to your computer without encryption. As shown in the image below, 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 be 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 time, 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: search. Click the search button 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:

Open Message Database File

After 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.

Export Messages

This way, you can obtain the desired message samples.

Reference#

SQL to extract messages from backup