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

26 May 2009 22 Comments

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.

iphone-sms-settings-wifi

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.

iphone-sms-sqlite-database-browser

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).

 

Related posts:

  1. Backup your iPhone’s Voicemail Messages
  2. Enabling remote access to a MySQL Database Server
  3. Configure Gmail IMAP accounts properly in Apple Mail and on the iPhone
  4. Importing or Exporting comma-separated (CSV) and other text-based files to / from MySQL databases
  5. Enable Emoji Characters on iPhones outside of Japan
Twitter Digg Delicious Stumbleupon Technorati Facebook Email
apple, databases, sqlite, tips & tricks, tools, unix
, , , , , , , , , , ,

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

  1. Alan D Reply 26 May 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…

  2. willem Reply 27 May 2009 at 11:00

    Thanks Alan, that’s a great tip!

  3. Cédric Reply 21 Jun 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…

  4. willem Reply 21 Jun 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.

  5. Cédric Reply 21 Jun 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 ;-)

  6. miguel Reply 07 Jul 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?

  7. willem Reply 09 Jul 2009 at 10:39

    Miguel: Which version of the iPhone OS are you running? 2.* or 3.0?

  8. David Reply 10 Aug 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!

  9. willem Reply 10 Aug 2009 at 13:40

    Thanks for letting us know, David!

  10. Cédric Reply 10 Aug 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 :-)

  11. Nokulunga Reply 19 Oct 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

  12. Mario Reply 21 Oct 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?

  13. Ezequiel Tolnay Reply 09 Nov 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

  14. willem Reply 09 Nov 2009 at 10:18

    Thanks, Ezequiel!

  15. Sam Reply 25 Nov 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

  16. Paolo Reply 02 Dec 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?

  17. Mustak Agrikli Reply 15 Dec 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.

  18. BigBoy Reply 08 Feb 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;

  19. BigBoy Reply 08 Feb 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;

  20. Mike Reply 17 Mar 2010 at 20:15

    It’s possible to make an export of the sms.db, make a change of the sms text with the SQLite Data Browser and after import the new sms.db to the iphone?
    I try it but I can’t change the text, when I try to save the new text I have a error “Data could not be updatetd”.
    Have someone one idee, how can I make it?

  21. nocheating Reply 17 Aug 2010 at 11:58

    #Download python 2.7 and run it in python GUI in the same directory as sms.csv
    #Python sms extractor
    #The whole point of it is the time.ctime fonction which extracts the date from the how_long_since_1-1-1970 format
    #
    #you need an uncripted saved iphone from itunes, then extract the message table from “Application Support/MobileSync/Backup/{some long string of hexadecimal characters}/3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata” with SQLitebrowser
    #
    #to filter nums containing ‘6568′, replace if 1: by if f[1].find(‘6568′)>-1:
    import time
    import csv
    import string
    fi = csv.reader(open(’sms.csv’, ‘rb’), delimiter=’,', quotechar=’”‘)
    f=fi.next();
    for k in range(1,8000):
    f=fi.next();
    datt=time.ctime(int(f[2]));
    try:

    if 1:
    if f[4]==’2′:#sent
    print ‘———————’+f[1]
    print ‘———————’+f[3]
    print ‘———————’+time.ctime(int(f[2]))
    else:#received
    print f[1]
    print f[3]
    print time.ctime(int(f[2]))

    #print f[1]

    except:

    pass

Trackbacks/Pingbacks

  1. Tom de Grunwald (anonymoustom) « Exporting your iPhone"s SMS Message Database to a CSV file | Geekology « Chat Catcher - 04 Nov 2009

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