Archiving iPhone SMS Messages to your Computer

February 24, 2010

(These instructions assume OS X.  Windows users, they should be adaptable.)

First, you’re going to need to sync your iPhone with iTunes.

Browse to Library/Application Support/MobileSync/Backup/<id>/3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata
(the 3d0d is fixed across systems – the <id> changes)

Copy this file to some safe location. This file is an SQL formatted database.

To generate a CSV file that’s easier to work with, run:
sqlite3 -csv -separator ‘,’ 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata “select * from message;” > output.csv

This will spit out a CSV file, with fields as follows.

MessageID; phone number text was sent to/received from; UNIX timestamp of message; message; incoming/outgoing (3 is outgoing, 2 is incoming); 0 (unsure purpose, but always 0 in my dbase); null; recipient ID; sometimes UNIX timestamp sometimes 0; 0 (again, unsure, but always 0); field that is either 0 or 4 (unknown purpose); 0; null; message encoding (all mine are us); null; null; 1 (unsure purpose – terminator?)

Load it into Excel, or whatever text processing language you enjoy, and bob is your uncle. Or your brother, your best friend, your ex-girlfriend, or whoever else you’re archiving texts from.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: