2012
12.20

As I wrote before, a few months ago my N900 (touchscreen) died and without a working touchscreen it proved to be impossible to operate it. Therefore I ordered a new one which could, but did not fix my phone.

In order to recover my text messages I wrote a simple Python script which reads the el-v1.db file in comm_and_cal.zip:/home/user/.rtcom-eventlogger/backup.tgz (which is an SQLite 3 database) and writes the messages to a CVS file (which are better handled by other phones).

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import sqlite3
import os.path
import csv
from datetime import datetime

if len(sys.argv) != 3:
  print ""
  print "Usage:"
  print "%s [path to el-v1.db] [path to csv file]" % sys.argv[0]
  print ""
  sys.exit(1)

dbFileName  = sys.argv[1].strip()
csvFileName = sys.argv[2].strip()

if not os.path.exists(dbFileName):
  print ""
  print "Error:"
  print "Could not open '%s'" % dbFileName
  print ""
  sys.exit(1)

with sqlite3.connect(dbFileName) as connection, open(csvFileName, 'wb') as csvFd:
  cursor = connection.cursor()
  writer = csv.writer(csvFd, delimiter = '\t', quotechar = '"', quoting = csv.QUOTE_ALL)

  sql = "SELECT start_time, remote_uid, free_text FROM Events WHERE event_type_id = 7"
  for record in cursor.execute(sql):
    startTime, phoneNumber, textMessage = record
    
    startDateTime = datetime.fromtimestamp(startTime).strftime('%Y-%m-%d %H:%M:%S');
    phoneNumber   = phoneNumber.encode('utf-8')
    textMessage   = textMessage.encode('utf-8')

    writer.writerow([startTime, startDateTime, phoneNumber, textMessage])

connection.close()
csvFd.close()

Enjoy!

References:

7 comments so far

Add Your Comment
  1. sam

    should that be
    sql = “SELECT start_time, remote_uid, free_text FROM Events WHERE event_type_id = 11”

  2. tersmitten

    The event types table looks like this for my phone, so 11 would be “Group chat joined”.

    n900 event types

  3. ojw

    I made these changes to skip garbled/unreadable lines:
    ====
    [ojw23@lnc n900.sms]$ diff -u get.py get-ojw.py
    — get.py 2016-03-30 16:35:18.086721635 +0100
    +++ get-ojw.py 2016-03-30 16:40:35.380593100 +0100
    @@ -32,10 +32,15 @@
    for record in cursor.execute(sql):
    startTime, phoneNumber, textMessage = record
    startDateTime = datetime.fromtimestamp(startTime).strftime(‘%Y-%m-%d %H:%M:%S’);
    – phoneNumber = phoneNumber.encode(‘utf-8’)
    – textMessage = textMessage.encode(‘utf-8’)
    + try:
    + phoneNumber = phoneNumber.encode(‘utf-8’)
    + textMessage = textMessage.encode(‘utf-8’)

    – writer.writerow([startTime, startDateTime, phoneNumber, textMessage])
    + writer.writerow([startTime, startDateTime, phoneNumber, textMessage])
    +
    + except Exception, e:
    + print e
    + continue

    connection.close()
    csvFd.close()
    ====

  4. Mischa ter Smitten

    Can you make a pull request?

    https://github.com/tersmitten/n900-backup-extractor

  5. Steve McIntyre

    I couldn’t find any SMS import tools that would work with the CSV output here, so I quickly extended/modified the eventlogger export script to output XML compatible with the backup format of “Backup SMS Pro”: https://play.google.com/store/apps/details?id=com.smobile.smsbackup&hl=en_GB

    Worked for me to convert ~11,000 messages from my n900 and then import into my new android phone

  6. agent24

    I’m trying to run this script but in all cases the output files end up empty.
    Have I done something wrong?

  7. Mischa ter Smitten

    I don’t know. How do you run it and what version (e.g. GitHub) are you using?