Since my iPhone 3GS died, I have been using my dad’s Samsung Jet as a temporary replacement phone. I really can’t stand the resistive touch screen – tapping backspace will at times hit the T9 button when I’m composing an SMS. Also, I miss the display of SMSes as a conversation with both sent and received messages in a single place.
I obsess over keeping chat history, so naturally I want to find a way to preserve these messages on the phone. I keep hitting the maximum limit of about 200 odd SMSes and I have to delete old messages. The first step would be to download the SMSes onto my PC, using the Samsung New PC Studio (NPS) software.
The Nokia PC Suite used to save the SMSes in some CSV-like format, so I wonder if the Samsung NPS does the same thing. Peering into the AppData directory, I found a couple of files and (empty) folders. I inspected the .dat files, and I found that they were of the type “Standard Jet DB”. To open them, I renamed them to .mdb and opened them in MS Access. One file had password protection, and the other did not. Using this other database, it seems that the sync-ed SMSes were stored in the MESSAGE table. Great!
So I began looking for a way to access this database. Since I’m using 64-bit Windows, I was having difficulty using the Windows Scripting Host (WSH) and the ActiveX objects, but JetSQLConsole worked fine. JetSQLConsole was written in C/C++ and it relied on QT, so recompiling it for my purposes is going to be a little bit troublesome. In its current state, it provides an SQL console to the database, but I would prefer programmatic access to the database, so eventually I settled for Python + pywin32 extensions.
iPhone SMS Database
The iPhone (or iOS, rather) uses an SQLite database to store the SMSes. In this database, there are a couple of tables, indices and triggers. The tables we are interested in is the message table, the msg_group table for grouping the SMSes into different conversations, and a mapping table called group_member.
Accessing this database will not be easy (or should I say convenient) if you do not jailbreak your iPhone. There are two methods for accessing this database:
- jailbreak the iPhone, then use something like iPhone Explorer to extract the SMS database
- sync to backup the iPhone, then locate this database in the backup to copy it
Since we are importing SMSes into the iPhone, the database will need to be copied back into the phone. If you choose to use method #2, you will need to perform a restore to get the updated database back into the phone.
Putting it Together
Armed with the knowledge of both databases and their schema, all that was left is to create a converter between the two. The typical use case would be:
- connect the Samsung Jet to download SMSes from the phone into NPS
- copy the iPhone SMS database (
/var/mobile/Library/SMS/sms.db) to the script directory - run the script to import the Samsung SMSes into the iPhone SMS database
- copy the updated iPhone SMS database back into the phone
Since I will be running it frequently, I did not want to have to worry about importing duplicate SMSes. I made the script check for duplicate SMSes using the SMS text, the timestamp and the number and direction (sent or received) it came from. With most older phones, sending a blank SMS was allowed; the script will ignore blank messages.
As I did not have iTunes on the Windows machine, I had to use my Mac, copy the database to the Mac, then to the PC, run the script, then copy it back to the Mac, and then onto the iPhone. To streamline this process, I decided to install iPhone Explorer and only the necessary dependencies, instead of everything that comes with iTunes. You will need only the AppleMobileDeviceSupport(64) and the AppleApplicationSupport packages.
The script uses the Python port of libphonenumber, which parses phone numbers. This is needed because my telco prefixes the country code even if the SMS is local, and obviously my address book doesn’t have those, so I needed a way to match these. The databases is filled with both versions of these numbers, depending on whether the SMS was outgoing or incoming, and sometimes the other party’s telco.
Download
I’ve made the script available on Bitbucket. You can use the “get source” link to grab a copy of it, or alternatively, by using Mercurial.
Update: I gotten my iPhone 4S since Nov 2011 and successfully managed to restore the SMS database from my iPhone 3GS backup.
The iPhone 3GS was running iOS 4.3.2. I ran my SMS import script every few days to update the iPhone’s SMS database, and I still connected my iPhone 3GS to iTunes to perform regular backups. When I got my new 4S, which was running iOS 5.0, I simply “restored” the latest backup to the new phone. All my SMS history (except those special cases I’ve mentioned) was on the new phone, as if I had been using an iPhone all the while.
As such, I will not be updating my script to work with iOS versions greater than 4.3.x. In iOS 5 the SQLite database schema would most probably have changed to accommodate iMessage. However, I would be happy to accept patches from someone who has implemented the functionality.
irq5.io 
[…] Anyhow, I bit the bullet and read through the ctypes documentation and wrote AMDevice.py which exposes some simple classes to handle connecting to an iPhone. I only implemented the minimal set of functions required to download and upload files to the iPhone, as I wrote this primarily for my iPhone SMS import script. […]
Hi,
I was just about to write the same thing as you in Python, when I founf your web page.
The only thing being different is, that I’m not importing from Samsung, but from Android, which gives me anyway the option of having the source in xml, csv as I like (there are plenty of apps).
I was not sure about the handling of sms groups, but cool that you figured out and implemented it. Just from interest, did you figure out what the hash row is used for?
I think also the backup will work in ios5, as the sms.db stays same, and another file (sms.db_wsahl or similiar) us used for imessage.
Thanks a lot,
Max
Glad I could save you some trouble. I still haven’t found out what the hash column was used for, but I don’t think it’s necessary to compute it correctly, since there’s no problems with the imported SMSes.
Interesting bit about the
sms.db_wsashlfile though. I might take a look at that when I have time 🙂If you try to change the adress in group_member table, you will see the hash gets a problem. 😦
Hey,
I have been searching for a way to do this with my Droid Incredible 2.. I would like to transfer my messages from my Droid to my new iPhone.. Will this work for me or was it written specifically for your Samsung?
Thanks,
Wyatt
This script was written to work with the Samsung NPS software. If you’d like to use this script, you will need to find some way to export the SMSes from your Droid (max in the above comment has mentioned that there’s plenty of apps) and modify this script to use the exported SMSes.
The import to iPhone should still be the same, assuming you’re using iOS 4.3.x. If you are using iOS 5.x, then I can’t guarantee that it would still work.
Okay, so I export my messages to my computer then how do I modify them to work?
And also, could I restore my iPhone to 4.3.x then update it to 5.0.1 and keep the texts?
Thanks for your help!
Wow.. I just took another look at this and I am so lost.. I tried downloading some things and getting everything ready but.. Its a little overwhelming. I apologize for my incompetence.. I’m just trying to figure this out.
Hi Wyatt, sorry to disappoint you but this script would require quite a bit of modifications if you’re not using the Samsung NPS software and iOS 4.3.x.
When I say modify, I don’t just mean change 1 or 2 lines. Depending on the export file format (could be XML, CSV, or some other format), the script needs to be rewritten to be able to understand the file, for example for a CSV file, which column is the sender’s number and which is the timestamp.
Additionally, it is not possible to downgrade iOS devices (or at least nobody found out how to circumvent that yet). You need to rewrite the script to make sure it works for iOS 5.x. I personally haven’t looked at getting it to work for iOS 5 – it might be trivial, or not.
Unless your conditions are exactly the same as mine, the script presented here would definitely not work out-of-the-box for you. As this blog is meant for a technical audience, I do not expect it to be installed and used by end-users but rather as a starting point for people to modify to suit their needs.
Okay, well I don’t have enough experience with scripts to be able to make this work for myself. Thank you anyways! Hopefully I can find a way somewhere.. There are so many ways to transfer from iPhone to Android but nothing from Android to iPhone.. Oh joy!
Hello how are you?I have an android phone and I was wanting to do what you did.im going to buy an iPhone but I want to transfer all my texts from this android phone directly to the iPhone.for me I have a new computer that is running a 64bit version of windows 7.but while the truth is this may be a long and lengthy process….can you please email me directly a step by step process on how to do this please?I know it may be alot.and im not that familiar with the actual database of both OS’s.but can you please help with this sir?
Hi, sorry to disappoint but currently this doesn’t work with iOS 5, and it also doesn’t work with Android phones.
It was specifically meant to work with iOS 4.3.x and a Samsung phone that connects to Samsung NPS software. If you’re not using both of those, then it’s not going to work for you at all.
There’s an option to Export the messages from message manager to a database file. It exports it with filetype “.nef” but if you just rename it to .mdb, it works fine… No need to dig into the APPDATA directory 🙂
Hi Sharun, I used to perform steps 2 to 4 by hand: I opened iPhone Explorer, navigated to the directory, copied the sms.db file out into the script directory, ran the script, then copied the file back. A couple of weeks later, I decided that I had to implement AMDevice (which does this for me).
So yes, I guess you could export the NEF, then rename it to .mdb, and then specify the path to the MDB file, but try doing this a couple of times per week and you’ll soon wish the script could do it for you.
Hi Darell. First of all thak you for the tutorial! I’ve been looking for this two days…
I have a little question: I have made my sms backup from my android phone (Samsung). Now I have a file called ARCHIVE.db. I have made which is in your tutorial, but I think that something that I made was wrong. Exactly, what do you mean when you say “ran the script, then copied the file back”. I put the file (previously I renamed it as “sms.db”) in the route /var/mobile/Library/SMS/sms.db, but nothing happened, so I think that one step I’m doing wrong.
When you says that in iExplorer:
“run the script to import the Samsung SMSes into the iPhone SMS database”
What do exactly have to do?
And I don’t understand either
“copy the updated iPhone SMS database back into the phone”.
I’ll be grateful if you could help me.
Thank you very much indeed and sorry if there are gramatical mistakes in this post, I don’t speak english very well.
Bye!
Hi Patricia, I am afraid you have misunderstood my instructions. You cannot directly copy the ARCHIVE.db file into the location
/var/mobile/Library/SMS/sms.db.I have written a script (like a program) that converts the SMSes into a format suitable for the iPhone. That is what I mean by “run the script”, which performs the import or conversion process.
Also, the script does not work with Android phones. I have mentioned this in one of the comments above. Sorry to disappoint.