copy_sms_from_n900_to_android

Copying SMS messages from Nokia N900 to Android phone.

I wanted to copy my messages from my old N900 phone under Maemo to my Samsung Galaxy S3.

I didn't find any useful tools for it, so I finally wrote a little script to do the work for me, using various tools I found.

Procedure

I used the info in the maemo forum to extract files from a backup of the data. The messages are stored in an SQLite database, stored in here: /home/user/.rtcom-eventlogger/el-v1.db I used a python script with SQLAlchemy to access the database.

To copy the contact names, I had to add an primary index 'id' column to the 'Remotes' database. I did that using SQLiteBrowser

Then, I loaded all the text messages in the database, and dumped them in an XML format readable by the SMS Backup & Restore app.

I copied the file on my phone and viewed it in the app, when it looked like it gave good results, I imported all the messages, et voilà!

Code

I wrote a python script to run the test. Feel free to adapt it to your needs, but run it at your own risk. It did work for me.

#! /usr/bin/env python
# vim: set fileencoding=utf-8 :

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Boolean, String, ForeignKey
from sqlalchemy.orm import mapper, sessionmaker, relationship, backref
from sqlalchemy.ext.declarative import declarative_base

#http://stackoverflow.com/questions/3095434/inserting-newlines-in-xml-file-generated-via-xml-etree-elementtree-in-python
# switch from elementtree to lxml so output can be pretty-printed
#import xml.etree.ElementTree as ET
import lxml.etree as ET

from datetime import datetime

Base = declarative_base()

class events(Base):
    __tablename__ = 'Events'
    id = Column(Integer, primary_key=True)
    event_type_id = Column(Integer, ForeignKey('EventTypes.id'))
    event_type = relationship('eventtypes')

    service_id = Column(Integer, ForeignKey('Services.id'))
    service = relationship('services')

    storage_time = Column(Integer)
    start_time = Column(Integer)
    end_time = Column(Integer)

    is_read = Column(Integer)
    bytes_sent = Column(Integer)
    bytes_received = Column(Integer)
    flags = Column(Integer)

    free_text = Column(String)

    local_uid = Column(String)
    local_name = Column(String)

    remote_uid = Column(String, ForeignKey('Remotes.remote_uid'))
    remote = relationship('remotes')
    group_uid = Column(String)
    channel = Column(String)


    outgoing = Column(Boolean)
    mc_profile = Column(Boolean)

class services(Base):
    __tablename__ = 'Services'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    desc = Column(String)
    plugin_id = Column(Integer, ForeignKey('Plugins.id'))
    plugin = relationship('plugins')

class plugins(Base):
    __tablename__ = 'Plugins'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    desc = Column(String)

class eventtypes(Base):
    __tablename__ = 'EventTypes'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    desc = Column(String)
    plugin_id = Column(Integer, ForeignKey('Plugins.id'))
    plugin = relationship('plugins')


class remotes(Base):
    __tablename__ = "Remotes"
    id = Column(Integer, primary_key=True)
    local_uid = Column(String, unique=True)
    remote_uid = Column(String, unique=True)
    remote_name = Column(String)
    abook_uid = Column(String)


engine = create_engine('sqlite:///el-v1.db')

metadata = MetaData(engine)

# events_table = Table('Events', metadata, autoload=True)
# mapper(events, events_table)

# eventtype_table = Table('EventTypes', metadata, autoload=True)
# mapper(eventtypes, eventtype_table)

Session = sessionmaker()
Session.configure(bind=engine)
session = Session()

#for k,v in Events.__dict__.iteritems():
#    print k, "\t", v

outputfile = "sms-export.xml"

root = ET.Element("smses")
comment = ET.Comment(u"Generated with sms_to_xml.py (http://www.lampshade.ch)")
root.append(comment)

count = 0

for i, e in enumerate(session.query(events).order_by(events.start_time).all()):
    if e.event_type.name == "RTCOM_EL_EVENTTYPE_SMS_MESSAGE":
        direction = ""
        if e.outgoing == True:
            direction = ">"
        else:
            direction = "<"
        print i, direction, u"{0} ({1})".format(e.remote.remote_name, e.remote_uid), e.free_text

        sms = ET.SubElement(root, "sms")

        #       protocol – Protocol used by the message, its mostly 0 in case of SMS messages.
        sms.set("protocol", unicode(0))
        #       address – The phone number of the sender/recipient.
        sms.set("address", unicode(e.remote_uid))
        #       date – The Java date representation (including millisecond) of the time when the message was sent/received. Check out www.epochconverter.com for information on how to do the conversion from other languages to Java.
        sms.set("date", unicode(e.end_time * 1000))
        #       type – Sent = 2, Received = 1.
        #       service_center – The service center for the received message, null in case of sent messages.
        if e.outgoing == True:
            sms.set("type", unicode(2))
            sms.set("service_center", unicode("null"))
        else:
            sms.set("type", unicode(1))        
            sms.set("service_center", unicode("null"))

        #       subject – Subject of the message, its always null in case of SMS messages.
        sms.set("subject", unicode("null"))
        #       body – The content of the message.
        sms.set("body", unicode(e.free_text))
        #       toa – n/a, default to null.
        sms.set("toa", unicode("null"))
        #       sc_toa – n/a, default to null.
        sms.set("sc_toa", unicode("null"))

        ##       to be completed
        sms.set("service_center",unicode("null"))
        #       read – Read Message = 1, Unread Message = 0. 
        sms.set("read", unicode(e.is_read))
        #       status – None = -1, Complete = 0, Pending = 32, Failed = 64.
        sms.set("status", unicode("0"))

        #       readable_date – Optional field that has the date in a human readable format.
        message_date = datetime.fromtimestamp(e.start_time)
        sms.set("readable_date", unicode(message_date.strftime('%b %d, %Y %I:%M:%S %p') ))
        #       contact_name – Optional field that has the name of the contact.
        sms.set("contact_name", unicode(e.remote.remote_name))

        ##      seen in export XML file
        #       date_sent
        sms.set("date_sent", unicode(e.start_time * 1000))

        #       locked
        sms.set("locked", unicode("0"))

        count += 1

root.set("count", unicode(count))

output = open(outputfile, "w")
output.write("""<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>\n""")
output.write("""<?xml-stylesheet type="text/xsl" href="sms.xsl"?>\n""")

tree = ET.ElementTree(root)
tree.write(output, encoding="UTF-8", xml_declaration=False,pretty_print=True)

EDIT(28.10.2012): applied a patch sent in by Ivo Anjo:

  • fixes the sorting of events in the file
  • switches to lxml to have some pretty printing of the XML output

Thanks Ivo, it's nice to see my little work is useful somewhere!

  • Created: 05/08/12 16:27
  • Modified: 28/10/12 12:44