Exporting your iPhone's SMS Message Database to a CSV file

26 May 2009

Exporting your iPhone’s SMS Message Database to a CSV file

Author: willem In: apple, databases, sqlite, tips & tricks, tools, unix

SMS messages on the iPhone are stored in a SQLite database, and if you need to back up your messages you can do so using one of the methods below.

Firstly you’ll need to Jailbreak your iPhone so you can get remote access to it via SSH. Download and use PwnageTool as per these instructions to Jailbreak the iPhone, then open Cydia and install the OpenSSH package if it hasn’t been installed already.

Launch the Settings app on the iPhone, choose “Wi-Fi“, then turn “Wi-Fi” on and connect the iPhone to your Wi-Fi network (or create a Wi-Fi network on your Mac by clicking the Airport icon in the menubar and choosing “Create Network…“, then connect the iPhone to that).

After the device connects to the network, tap the blue right-pointing arrow next to your Wi-Fi network entry in the Settings app and make a note of the iPhone’s IP Address on the network.

Open a new Terminal window on your Mac, then enter this command to copy the iPhone’s SMS Database file to your Desktop (replace “{IP_ADDRESS}” with the IP Address you noted above):

scp root@{IP_ADDRESS}:/var/mobile/Library/SMS/sms.db ~/Desktop/

When you enter the scp command you will be prompted for a password, simply enter the default iPhone root password (‘alpine‘).

Exporting the iPhone SMS Database to a CSV file using the command line:

Open a new Terminal window, then enter these commands (assuming that the SMS Database file is stored on your Desktop and that you want to export the SMS messages to a file named “output.csv“):

cd ~/Desktop
sqlite3 -csv -separator ',' sms.db "select * from message;" > output.csv

Exporting the iPhone SMS Database to a CSV file using a GUI tool:

The GUI Tool used in this example is called SQLite Database Browser and can be downloaded from here.

Download and install the application, then run it. Click the “File” menu, then choose “Open Database“. Choose your SMS Database file and click the “Open” button, then switch to the “Browse Data” tab. Choose the “message” table, then click the “File” menu and choose “Export“, “Table as CSV File“. Choose the “message” table and click the “Export” button, then choose a location to save the file to.

Explanation of fields:

The iPhone SMS Database’s “message” table contains several fields of which the most relevant ones are:

rowid: A unique identifier for each message
address: The telephone number of the sender / recipient
date: The Unix Timestamp of when the message was sent / received
text: The message content
flags: 3 Indicates that the message was sent, 2 indicates that it was received
group_id: Indicates the conversation the message belongs to (messages are linked together via groups and the group_member table).

Share this article: These icons link to social bookmarking sites where readers can share and discover new web pages.

Related posts:

Tags: backup, csv, cydia, database, export, iphone, jailbreak, openssh, scp, sms, sqlite, ssh

Like this post? Subscribe to the Geekology RSS 2.0 feed!

20 Responses to “Exporting your iPhone’s SMS Message Database to a CSV file”

Alan D
May 26th, 2009 at 17:10
If your phone isn’t jailbroken, you’re still OK - the SMS database is backed up by iTunes - it’s in the backup directory:
```
~/Library/Application Support/MobileSync/Backup/{some long string of hexadecimal characters}/
```
and on my Mac, the filename is:
```
3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata
```
Rename that to ‘sms.db‘ and open it.

You can find the file by going into that directory, and typing
```
grep sms.db *
```
and it’ll tell you something like:
```
Binary file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mdinfo matches
```
That tells you that the file with the same base filename string, but an extension of ‘.mddata‘ is the SMS database. COPY that to a filename like:
```
cp  3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata ~/sms.sqlite
```
and then you can open the sqlite file the same way this tip said to…
willem
May 27th, 2009 at 11:00

Thanks Alan, that’s a great tip!
Cédric
June 21st, 2009 at 19:57

Thanks Willem !
I was looking for a way to read my sms.db ! I’ve just upgraded my iPhone from 2.2.1 to 3.0, used SSH to replace the file but it doesn’t work, the db format may be different…
But with your help, my sms aren’t lost

Would be great if there was a way to convert the previous sms.db to the new 3.0 db format…
willem
June 21st, 2009 at 20:34

Hi Cédric

Thanks for letting me know, I’ll take a look at the 3.0 SMS database format.

In the meantime you could apply one of the backups iTunes made before you installed the new software to your iPhone to recover all of your messages and settings.
Cédric
June 21st, 2009 at 21:49

You’re right ! But I always prefer to start with a clean system, and sms.db (and bookmarks.plist) were so easy to ‘ move ‘ with SSH… But now I know where are the iTunes backup files
miguel
July 7th, 2009 at 20:45

i cannot see any data on sqlite browser when i open the sms.db file. I just see the headers. when i open the sms.db file using text pad i am able to see messages.
any idea of what is going on?
willem
July 9th, 2009 at 10:39

Miguel: Which version of the iPhone OS are you running? 2.* or 3.0?
David
August 10th, 2009 at 13:29

you could also just aswell use DiskAid for iphone… Mobilesyncbrowser (costs money) and IPhone Backup Extractor are also great apps.

…and about moving sms.db : I had saved that file while running 3.0 … then however messed things up by deleting it on the phone (when copying back it could display stuff but no longer receive/send or would entirely crash) but in the end I got things right by restoring to an older backup and then replacing the sms.db file using diskaid. just thought that might help somebody..
cheers guys!
willem
August 10th, 2009 at 13:40

Thanks for letting us know, David!
Cédric
August 10th, 2009 at 16:09

Hi,
Just some news about my issues with sms.db, from 2.2.1 to 3.0 : everything is now ok ! I just had to change the ‘ file permissions ‘ of the file to 0777 ! SSH, Transmit to put the sms.db in the good folder and change the values to 0777, that’s fine now
Nokulunga
October 19th, 2009 at 22:23

I have an iphone and suddenly out of the blue I can’t send sms’s vodacom says I must cange the message center number but they dont know how to go about doing that.I’ve even been to their vodacare center but recieved no help from them
Mario
October 21st, 2009 at 14:32

Hi,

I have a contact list file from my 3g firmware 2.2.1 and i want to put it on my 3g 3.1.2 via ssh…but when ever i transfer the file from my pc to the iphone it will not show the old contact list! any clues?
Tom de Grunwald (anonymoustom) « Exporting your iPhone"s SMS Message Database to a CSV file | Geekology « Chat Catcher
November 4th, 2009 at 01:13

[...] But then this sounds quite fun too… [link to post] . Might not even need to [...]

Ezequiel Tolnay
November 9th, 2009 at 00:21

Hi all, I’ve made a script to simplify the process extracting the SMSs from the iTunes iPhone backup and format the output for legibility:

#!/bin/bash
# Extracts SMSs in CSV format from iPhone backup
SQL="select datetime(date, 'unixepoch', 'localtime') as \"date\", case when flags=3 then 'sent to' else 'rec from' end as \"from\", address as phone, text as \"message\" from message;"
FILE="`dirname "$HOME/Library/Application Support/MobileSync/Backup/*"`/654eed8e432c2c2bd3ff723878b686193281d9bf/3d0d7e5fb2ce2888133
06e4d4636395e047a3d28.mddata"
sqlite3 -csv -header -separator ',' "${FILE}" "${SQL}"

redirect the output to a file, or add the redirection straight to the code. E.g. ./iphone-sms.sh > sms-backup.csv

willem
November 9th, 2009 at 10:18

Thanks, Ezequiel!
Sam
November 25th, 2009 at 03:22

Um hey im kind of a noob at scripts and terminal and stuff like that and every time some post code like that i have absolutely no idea where to type that into. So can anyone help me out where does Ezequiel’s code get entered into ive tried everywhere i could think of…

Thank You all for your wonderful support! Seriously
Paolo
December 2nd, 2009 at 10:34

Worked great for me! Thanks for this well done tutorial The only problem, I’m in china, and when I export to csv file I cannot see chinese chars, while they appear correctly on SQLite. Don’t think it depends on excel, since textedit also visualizes them as “?”. Of course I have asian languages installed on my mac, and I use them all the time.. maybe something goes wrong in the export?
Mustak Agrikli
December 15th, 2009 at 19:39

I just would like to share what I did! It may be helpfull for someone.

I have an original Iphone I did not want to Jailbreak. I backed up my phone and contents are stored at C:\Users\Mustak\AppData\Roaming\Apple Computer\MobileSync\Backup folder.

The folders of all backup data are there. I copied the most recent folder somewhere else, I renamed the extensions *.mddata to *.db.

Then I seached all the files which contains “SQLite”, because SQLite database file starts with “SQLite” word.

Then I dragged the found files one by one to the SQLite database browser, then I found all the data related Contacts, SMS, Calendar and Notes. There are a lot statistical databases as well.
BigBoy
February 8th, 2010 at 03:42

If anyone finds it useful, this is the query I ran to export my Calendar:

SELECT datetime(start_date, ‘unixepoch’, ‘localtime’), datetime(end_date+978307200, ‘unixepoch’, ‘localtime’), summary, location, description FROM Event order by 1, 2;
BigBoy
February 8th, 2010 at 03:43

If anyone finds it useful, this is the query I ran to export my Calendar:

SELECT datetime(start_date+978307200, ‘unixepoch’, ‘localtime’), datetime(end_date+978307200, ‘unixepoch’, ‘localtime’), summary, location, description FROM Event order by 1, 2;