Exporting iPhone Text Message (SMS) to PC for Free

There are many tools available for exporting iPhone text messages to external formats. However, it’s pretty easy to export text messages from an iPhone backup with no specialized software necessary. The steps below only export text messages – I still haven’t figured out how to export photos or other media. Not a big deal for me.

1) Start with an unencrypted backup created in iTunes. (If you encrypt, do an unencrypted backup, copy the folder as outlined below, then switch encryption back on.) On a Windows PC backups are located in: C:\\Users\\[user]\\AppData\\Roaming\\Apple Computer\\MobileSync\\Backup. To quickly get to the AppData folder, type “%appdata%” into a Windows Explorer address bar.

2) Each iTunes backup has a separate folder within Backups, which are named with a long blob of text. To find the correct folder just look at date modified, which should roughly tie to the last backup date.

3) Open the correct folder and search for the string “3d0d7e5fb2ce288813306e4d4636395e047a3d28″. This is your database file containing text messages. Copy this file to another folder, where you’ll open it and extract the messages.

4) The database file can be read using any SQLite reader. I used SQLite Browser, but any database reader should do the trick. Rename the file extension to .db so the SQLite reader recognizes it.

5) Open the database file with your SQLite browser. You’ll notice there are several tables containing source, time, and messages. To get usable information from this you’ll need to perform a join. Run the following query within the SQLite browser.

SELECT message.ROWID, message.guid, message.text, message.cache_has_attachments, strftime('%Y-%m-%d %H:%M:%S', message.date + 978307200, 'unixepoch') as datetime, message.is_from_me, handle.id FROM message INNER JOIN handle ON message.handle_id = handle.ROWID ORDER BY message.date ASC;

The query comes from someone smarter than me that a quick GitHub search returned. The query returns row ID, message ID, message (text), attachment flag, date, isfromme, and phone number fields.

sqlite

6) Copy and paste the results of the query into Excel or similar program where you can filter, sort, and pivot as necessary. I changed all 0s in the isfromme field to no, and the 1s to yes, then color-coded the yes rows blue to mimic the iMessage coloring. That’s it – pretty simple!

stats