View Single Post
Posts: 26 | Thanked: 16 times | Joined on Mar 2010
#9
As toojays already said, current backups have a slightly different column count and order. I've had a lot of fun with the csv export from the database, especially with commas and newlines in the messages. I finally gave up, exported with a different delimiter, and did some magic with libreoffice. This slightly changed script ran fine:

Code:
#!/usr/bin/perl

use strict;
use warnings;
use Time::Piece;

my %sms;
my $numsms = 0;

# Dump the SMS from an N900 backup file:
# 1. Open the backup folder and use an unpacker to open comm_and_cal.zip.
# 2. Navigate to /Root/home/user/.rtcom-eventlogger/ in the archive.
# 3. Open the backup.tgz archive.
# 4. Extract el-v1.db and open it using "SQLite database browser".
# 5. Select "File" -> "Export" -> "Table as CSV file".
# 6. Choose table "Events" and store the file.

# The file now contains the SMS along with some other events. Double
# quotes (") in the SMS text are replaced by "" (double double quotes)
# and new lines are still present. This script takes care of the former
# and replaces them by '' (two apostrophes), but it doesn't handle the
# latter. This makes some quasi-manual preprocessing necessary, which
# can be done using the Geany text editor:
# Replace all ([^"])\n by \1 to get rid of wrapped lines. Repeat this step
# until the pattern is not found anymore.
# If you ignore this step, multi-line messages will be silently discarded.

# When this is done, the script can be run by supplying the converted
# CSV file as standard input and redirecting standard output to another
# CSV file:
# ./n900_smsconv.pl < sms_in.csv > sms_out.csv

# An application like "SMS Tools" can then be used to import the SMS
# into your Android phone (choose the nokia format when asked).

# The database contains some definitions for the various IDs, those
# are shown in the following lists:

# service_id field:
# 1:  RTCOM_EL_SERVICE_CALL
# 2:  RTCOM_EL_SERVICE_CHAT
# 3:  RTCOM_EL_SERVICE_SMS

# event_type_id field:
# 1:  RTCOM_EL_EVENTTYPE_CALL
# 2:  RTCOM_EL_EVENTTYPE_CALL_MISSED
# 3:  RTCOM_EL_EVENTTYPE_CALL_VOICEMAIL
# 4:  RTCOM_EL_EVENTTYPE_CHAT_MESSAGE
# 5:  RTCOM_EL_EVENTTYPE_CHAT_NOTICE
# 6:  RTCOM_EL_EVENTTYPE_CHAT_ACTION
# 7:  RTCOM_EL_EVENTTYPE_CHAT_AUTOREPLY
# 8:  RTCOM_EL_EVENTTYPE_CHAT_JOIN
# 9:  RTCOM_EL_EVENTTYPE_CHAT_LEAVE
# 10: RTCOM_EL_EVENTTYPE_CHAT_TOPIC
# 11: RTCOM_EL_EVENTTYPE_SMS_MESSAGE

# flags field:
# 1:  RTCOM_EL_FLAG_CHAT_GROUP
# 2:  RTCOM_EL_FLAG_CHAT_ROOM
# 3:  RTCOM_EL_FLAG_OPAQUE
# 4:  RTCOM_EL_FLAG_OFFLINE
# 5:  RTCOM_EL_FLAG_SMS_PENDING
# 6:  RTCOM_EL_FLAG_SMS_TEMPORARY_ERROR
# 7:  RTCOM_EL_FLAG_SMS_PERMANENT_ERROR


foreach my $csvline (<STDIN>) {
  chomp $csvline;
  if ($csvline =~ m/
      (\d+),                # 1  id                 e.g. "3131"
      (3),                    # 2  service_id         e.g. "3"
      (7),                    # 3  event_type_id      e.g. "11"
      (\d+),                # 4  storage_time       e.g. "1396603341"
      (\d+),                # 5  start_time         e.g. "1396603339"
      (\d+),                # 6  end_time           e.g. "1396603341" or "0"
      (\d+),                # 7  is_read            e.g. "1"
      (\d+),                # 8  flags              e.g. "0"
      (\d+),                # 9 bytes_sent         e.g. "0"
      (\d+),                # 10 bytes_received     e.g. "0"
      ([^"]*),             # 11 local_uid          e.g. "ring tel ring"
      ([^"]*),             # 12 local_name         e.g. "<SelfHandle>"
      ([^"]*),             # 13 remote_uid         e.g. "+4179..."
      ([^"]*),             # 14 channel            e.g. ""
      (.*?),                 # 15 free_text          e.g. "abcdef"
      ([^"]*),             # 16 group_uid          e.g. "6288879"
      (\d+),                # 17  outgoing           e.g. "0" or "1"
      (\d+)                 # 18  mc_profile           e.g. "0" or "1"
      $/px)
  {
    $sms{$numsms}{'sent'} = int($17);
    $sms{$numsms}{'time'} = $5;
    $sms{$numsms}{'number'} = $13;
    $sms{$numsms}{'msg'} = $15 =~ s/""/''/ro;
    ++$numsms;
  }else{
    print STDERR "not parsed: ".$csvline."\n";
  }
}

print STDERR "Parsed $numsms messages.\n";

foreach my $key (sort {$a<=>$b} keys %sms) {
  print '"sms",';
  my $number = $sms{$key}{'number'};
  my $time = localtime($sms{$key}{'time'})->strftime('%Y.%m.%d %H:%M');
  my $text = $sms{$key}{'msg'};
  if ($sms{$key}{'sent'}) {
    print '"SENT",';
    print '"",';
    print "\"$number\",";
  } else {
    print '"READ,RECEIVED",';
    print "\"$number\",";
    print '"",';
  }
    print '"",';
    print "\"$time\",";
    print '"",';
    print "\"$text\"\n";
}
In case it doesn't, it's always helpful to run the perl debugger and/or some online one-click perl interpreter for fast debugging. A demo line of a valid message always helps as well.


Changed back my commas and newlines in the csv output and installed the "Sms Tools - All in one" (Valiero Finazzo - thanks man!) from the app store. Sent the stuff to the device, hit import, done! (backup your current messages first, you know...)

Well, guess my N900 is now ready for retirement after 7.5 years of service...
 

The Following 2 Users Say Thank You to Bzzz For This Useful Post: