The Problem at Hand...
So maybe you're looking at this page because you just want to dig up some old texts from a good friend that made you laugh real hard. Or maybe it's something worse. You bought a new phone and for some reason or other, the texts didn't get transferred over. Even if you haven't lost the texts on your phone, using the iPhone interface to look at the oldies is obviously not a good solution. Who wants to scroll and click the "Load Earlier Messages" button a few hundred times?
The good news is that if you back up your phone on a regular basis, there is a copy of all your text messages on your computer. In fact, everything on your phone is backed up. And most of the important stuff is in the following directory (on a Mac):
/Users/[Username]/Library/Application Support/MobileSync/Backup
where [Username] is your username on the computer. The Library folder is hidden by default on your machine, and if you need instructions on how to reveal it, you can find them here. If you've never used Terminal before, just search for it in spotlight and open it from there.
If you take a look in this Backup directory, you'll see an array of subdirectories with extremely confusing names, as shown below. Open these directories and you'll find even more files with confusing names. Well, don't fret. The purpose of this webpage is to guide you through them to find what you are looking for.
Note for Windows Users : although this path doesn't exist for iPhone users
that sync their device with a Windows computer, the file containing the SMS database is still on
your computer. Here are the directory paths on Windows boxes:
Windows XP - C:\Documents and Settings\[USERNAME]\Application Data\Apple Computer\MobileSync\Backup\
Windows Vista or 7 - C:\Users\[USERNAME]\AppData\Roaming\Apple Computer\MobileSync\Backup\
where USERNAME is your computer username and C:\ is the most likely drive for your root directory.
What You Will Need to Dig Up Your Texts
The instructions on this page require you to buy no new additional software. Nice, right? You probably did a search for "downloading iPhone text messages" or something similar and found a whole smorgasbord of different applications that will do just what you want. But they all cost upwards of $20. So if you are like me, you wanted to do it the good ol' fashioned way, which is free.
If you want to follow the method I'm gonna outline, you will still need to download some software, though. And there is some hardware required, of course. Here is a list of what you will need:
- An iPhone - This is kind of obvious, I guess. I've only tested this for an iPhone 3G and iPhone 4S, which covered iOS 4-6.
- A computer where the iPhone is backed up - Mac or Windows, although I'm sure there is a way to do it on Linux.
- SQLite Manager on Firefox - An app that can be downloaded for free. There are other free SQLite browsers out there, so feel free to pick your favorite.
- Microsoft Excel - This is not really necessary, but it'ss easier to handle for most than the SQL database format.
If you're looking at this list and you're thinking you'd rather just fork over the $20 for a tool with a nice interface to look at your texts, I feel you. In trying to find a free one, I found DiskAid to be a good candidate. I only used the free edition, though. There are a few other good ones out there as well, judging from reviews.
Finding Your SMS Text Message File
Ok, so getting back to that Backup directory, you'll probably see on your machine that there a number of subdirectories. On my machine, the two relevant directories are :
8e00ab3f605d5bb68c93cadf0eeacae935d809c7 iPhone 4S Directory
f4988a2ddd75844f626aacdd5e8e67103eaa9e02-20111112-131153 iPhone 3G Directory
The directories on your machine most likely have completely different names (I have to be honest, I have no idea what these byte sequences actually mean, but I know they are particular to your unique device). Regardles, if you look in the right directory, you'll find the following files (among many, many others):
3d0d7e5fb2ce288813306e4d4636395e047a3d28 SMS Text Message File
31bb7ba8914766d4ba40d6dfb6113c8b614be442 Contacts File
2041457d5fe04d39d0ab481178355df6781e6858 Reminders File
ca3bc056d4da0bbf88b5fb3be254f3b7147e639c Notes File
2b2b0084a1bc3a5ac8c27afdf14afb42c61a19ca Call History File
The first file should contain all of your texts. It's acutally an SQLite database, and all that's required to read it is an SQLite browser.
In order to view the database, open the SQLite Manager (open Firefox and go to Tools -> SQLLite Manager) and load the file (by clicking on the "Open" icon and selecting 3d0d7e5... entry). This will load the database, but at this point, you won't be able to see any really useful information. What you need to do is type the following in the "Enter SQL" text box:
iPhone 3G (iOS 4)
select message.date, handle.id, message.text from message, handle where message.handle_id=handle.rowidiPhone 4S (iOS 5-6)
select message.date, message.address, message.text from message where message.ROWID=message.ROWID
and press "Run SQL". Once you've done this, you should see something like the following (this is for my iPhone 4S):
Ok, so you may say, "I'm still seeing crazy numbers". But I can assure you they are making more sense now. Here's what is shown in the SQLite browser.
- The first column contains the date of the text message in some very funky, nonintuitive format. This will be better after they are loaded into Excel.
- The second column contains the phone number or email that pertains to it. I'm sorry if you were hoping that it would show the name of the contact. I'm still working on that...
- The third column contains the text string!
The SQLite Manager has a bit of functionality in terms of sorting, but it doesn't have the kind of search functionality that a program like Microsoft Excel does. So to put this in a format that Excel can read, click on the "Actions" pull-down and select "Save Result (CSV) to File". Save the file in a location where you can easily open it in the next step (like on your desktop).
Loading the Texts into Microsoft Excel
Find your .csv file from the previous step, right click on it, and select "Open with -> Microsoft Excel". Excel will load the file with the same three columns you saw before.
1) Formatting the Date
The first step will be to put the date column in a decent, human-readable format. To do this, insert a new column after column A. You should now see a blank column in column B. Select the first cell in column B (B1) and type the following:
iPhone 3G (iOS 4)=(((A1/60)/60)/24)+DATE(1970,1,1)+(-UTC/24)iPhone 4S (iOS 5-6)
=(((A1/60)/60)/24)+DATE(2001,1,1)+(-UTC/24)
The first term in the equation, (((A1/60)/60)/24), turns the number of seconds into the number of days. The second term is the starting point in time, which happens to be January 1, 1970 for the earlier iOS's and January 1, 2001 for later ones. Don't ask me why they chose this. The last term is the UTC offset of your local time zone (the numerator). So you may have to play around with this. If you live in the contiguous US, it's somewhere between -5 and -8, depending on Daylight Savings time.
Here's what it should look like after entering the formula for B1 (note that the figure below shows the texts before they've been sorted):
Do a copy of the contents of cell B1 and apply it to all the other cells in column B. An easy way to apply it to all the other cells is to click on B2, scroll all the way to the last row, and while holding down the Shift key, click the last cell in B (e.g. B42006), and then paste.
Once this is done, you will want to put column B into a recongizable format. So select column B, right click and choose "Format Cells". Go to the "Number" tab, select "Date" under the Category field, and choose whatever format you want. My preference is the one shown in the figure below.
After clicking on "Ok", you should now see a bunch of dates and times in column B.
2) Sorting Everything Out
Now, you may just want to do a simple sort on the date. But chances are that you will want to sort by phone number/email and then do a sub-sort by date. To do that:
- Click on the "Data" tab in Excel.
- Click on the "Sort" icon at the far left (shown in the image above). Select "Custom sort".
- Click on the "Column" field and select column B.
- Click on the "+" icon at the bottom. This should bring up another row in the sorting area.
- Click on the "Column" field for that row and select column A.
You should see the following:
Now the texts are sorted first by phone number/email and then by the date received. Your outgoing texts are interleaved in there as well.
So there you have it! I know it's not the most visually appealing way to see your texts. No attachment images, no nice colored text bubbles, and so on and so on. But at least you have most of the hard, incriminating data you were after :-)