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, the interface will appear as shown below. You can see the backup records. Right-click and select
Show In Finder to open the folder.
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.
The search results are as follows:
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:
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;
Execute in the upper right corner. You can see that the messages have been filtered out.
Then select all rows, right-click and select
Export result set as, and export as
CSV to export the file in Excel format.
This way, you can obtain the desired message samples.