Export iPhone Text Messages to a Spreadsheet

iPhone 2.0 only: Want to save that string of flirty text messages for posterity (or evidence)? While there’s no super-easy way to get your SMS history off your iPhone, with some elbow grease it’s possible. While this method is a bit complicated and more proof of concept than anything else, let’s take a look at how you can FTP into your iPhone, transfer your text message database to your computer, and open it up in a spreadsheet application like Excel.

Nerd alert! This method requires a jailbroken iPhone and some FTP and DB skills. The end result does give you a spreadsheet-friendly file that includes your entire history of text messages in it, but no names of the people who sent the message (only the phone number). File this post under “proof of concept” instead of “must actually do.” If anyone knows of an iPhone application that saves text messages to your computer without going through this rigmarole, post it up in the comments and I’ll add it here.

Here’s one messy way to do it:

1. Jailbreak. First, you’ll need to jailbreak your iPhone using these instructions (here’s the Windows user version). Make sure you install OpenSSH (here’s how) to make your iPhone accessible via SFTP.
2. SFTP into the phone. Go to the phone’s network settings, and view the details of the active connection. Write down the device’s IP address. Using your favourite FTP client, SFTP into that IP address using the username root and password alpine.
3. Download sms.db. Your text message database is located in /var/mobile/Library/SMS/sms.db on your iPhone. Download that file to your computer.
4. Open the SQLite database. That sms.db file is a SQLite database, which you can edit and view using free tools. Download one of those tools—the SQLite Database Browser—and install it on your computer. Open up the sms.db file, and click on the Browse Data tab. Choose the message table and you’ll see your SMS history, and it’ll look like this:
5. Export to CSV. From the SQLite Database Browser’s File menu, choose Export > Table to CSV file. Save the resulting comma-delimited file, and open it using your spreadsheet of choice (whether it’s Excel or OpenOffice.org).

Now, the big problem with the resulting file is that while it includes the messages themselves, the name of each person who sent each message is not there. But on closer inspection, it turns out that the “address” field displays the phone number of the message sender. Given that, you can easily grok who’s who (and add names based on that number into your spreadsheet).

Is this living to geek instead of geeking to live? Yes it is. But I don’t judge how people get their kicks, so you shouldn’t either.