maemo.org - Talk - Export SMS from N900 to Android

Page 1 of 2

1

Show 40 post(s) from this thread on one page

maemo.org - Talk (https://talk.maemo.org/index.php)

- General (https://talk.maemo.org/forumdisplay.php?f=7)

- - Export SMS from N900 to Android (https://talk.maemo.org/showthread.php?t=51822)

BabylonV2000

2010-05-04 08:35

Export SMS from N900 to Android

Hi ,

I got an HTC Desire under Android 2.1 .

I am looking for a method to export my sms from N900 to Android.

I know a method who can import sms in file .csv from pc suite.

Or do you know an application on N900 who can generate a .csv file structured like pc suite one's ?

Thanks for advance for your help. :)

BabylonV2000

2010-05-05 14:23

Re: Export SMS from N900 to Android

Hi ,

No answer to my Thread :=((
Ok when i backup my message where the file is stocked ?

i hope the backup file is in .csv.

Thanks for your answer.

BabylonV2000

2010-05-07 12:02

Re: Export SMS from N900 to Android

Hi,

After some googling i found my answer
1. export sms from n900 with smsexporter (cf http://talk.maemo.org/showthread.php?t=37354)
2. import them in Android (cf http://libe.toile-libre.org/?p=530)

Hope it could help.

Thanks for reading

motom

2010-09-16 13:13

Re: Export SMS from N900 to Android

Quote:

Originally Posted by BabylonV2000 (Post 647921)

Hi,

After some googling i found my answer
1. export sms from n900 with smsexporter (cf http://talk.maemo.org/showthread.php?t=37354)
2. import them in Android (cf http://libe.toile-libre.org/?p=530)

Hope it could help.

Thanks for reading

did you export sms from smsexporter?

Cheers

inzimam

2011-05-25 19:19

Re: Export SMS from N900 to Android

guys, any update on this? link under 2. doesn't work

Rotsopp

2011-06-15 17:38

Re: Export SMS from N900 to Android

I managed by exporting my SMSes in .csv format from Nokia PC suite.
By using Ath2Sms from Android market i could import them into gingerbread. Had to do it separately for sent and recieved SMSes.

First I tried to export with Ovi suite but Ath2Sms did not recognize .cvs format from Ovi.

phip	2014-04-04 19:28

Re: Export SMS from N900 to Android

Hi all,

I know that this thread is old, but probably there are still people around here needing to import N900 SMS on Android. I finally had to bury my beloved Nokia after the second Touchscreen started to malfunction as well :(

Anyway, I do not have access to a working Ovi Suite for exporting my SMS, so I used a Backup copy of the phone (created using the Backup & Recovery tool on the N900) for that. This is just a folder containing some zip archives, containing more folders and archives and finally there's an SQLite database containing the SMS. A tool like SQLite database browser can then be used to export the table into a CSV file, which in turn can be converted to the standard Nokia format understandable for e.g. SMS Tools on Android. For this conversion, I've written a little Perl script, see below (n900_smsconv.pl). This also contains some more usage information.

Maybe it helps anyone.

Cheers,
Philipp

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"

      "(11)",                 # 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  outgoing           e.g. "0" or "1"

      "(\d+)",                # 9  flags              e.g. "0"

      "(\d+)",                # 10 bytes_sent         e.g. "0"

      "(\d+)",                # 11 bytes_received     e.g. "0"

      "([^"]*)",              # 12 local_uid          e.g. "ring tel ring"

      "([^"]*)",              # 13 local_name         e.g. "<SelfHandle>"

      "([^"]*)",              # 14 remote_uid         e.g. "+4179..."

      "([^"]*)",              # 15 channel            e.g. ""

      "(.*?)",                # 16 free_text          e.g. "abcdef"

      "([^"]*)"               # 17 group_uid          e.g. "6288879"

      $/pxo)

  {

    $sms{$numsms}{'sent'} = int($8);

    $sms{$numsms}{'time'} = $5;

    $sms{$numsms}{'number'} = $14;

    $sms{$numsms}{'msg'} = $16 =~ s/""/''/ro;

    ++$numsms;

  }

}



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";

}

toojays

2016-11-29 11:29

Re: Export SMS from N900 to Android

Quote:

Originally Posted by phip (Post 1419927)

Hi all,
I know that this thread is old, but probably there are still people around here needing to import N900 SMS on Android. I finally had to bury my beloved Nokia after the second Touchscreen started to malfunction as well :(

Thanks Phillipp. This script was a big help to me.

A couple of notes for anyone else needing to do this conversion:

1) Check that the order of the CSV fields in csvline matches up against the CSV you exported from the database browser. I had to reorder some fields, add a new field (mc_profile), and change the event type ID from 11 to 7 to get it to work.

2) When using SMS Tools to do the import, if you don't allow it to be the default SMS app the import will appear to do stuff, but won't actually achieve anything. So you need to allow it to be the default app. After the import it will offer to make Messaging the default SMS app again.

Bzzz	2017-08-02 15:56

Re: Export SMS from N900 to Android

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... :(

lameventanas

2018-03-09 06:44

Re: Export SMS from N900 to Android

In case anyone is still migrating away from Maemo, I tried to use the previous script but it didn't work for me, I think the schema was different.

So I wrote my own script, it takes the db as first argument (file called el-v1.db) and optional csv output file as second one.

Then I used an Android program called "Anything With SMS" to import the CSV. Finally I used another Android program called "Mark All SMS Read" to (surprise!) mark all SMS as read.

Code:

#!/usr/bin/perl

#

# Export Maemo N900 SMS to CSV

# Allows importing to Android phones using "Anything With SMS"

#

# Use this format when importing with Anything With SMS:

# "$(folder)","$(address)","$(dateyyyy-MM-dd HH:mm:ss)","$(body)"\s*

#

# Written by Alan

#

use strict;

use warnings;

use Text::CSV;

use DBI;

use POSIX;



# SQLite format from n900 (file: el-v1.db)

# From an exported backup, in comm_and_cal.zip -> Root/home/user/.rtcom-eventlogger/backup.tgz -> el-v1.db

#

# CREATE TABLE Events (

# id INTEGER PRIMARY KEY,

# service_id INTEGER NOT NULL,

# event_type_id INTEGER NOT NULL,

# storage_time INTEGER NOT NULL,

# start_time INTEGER NOT NULL,

# end_time INTEGER,

# is_read INTEGER DEFAULT 0,

# outgoing BOOL DEFAULT 0,

# flags INTEGER DEFAULT 0,

# bytes_sent INTEGER DEFAULT 0,

# bytes_received INTEGER DEFAULT 0,

# local_uid TEXT,

# local_name TEXT,

# remote_uid TEXT,

# channel TEXT,

# free_text TEXT,

# group_uid TEXT

# );



# Output columns in the order expected by import program (Anything with SMS)

my @out_cols = ('folders', 'number', 'time', 'msg');



die "Usage: $0 <sqlite input> <csv output>" unless $#ARGV == 1;



my ($dbfile, $csvfile) = @ARGV;



my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile, '', '', {RaiseError => 1, PrintError => 0});

my $csv = Text::CSV->new( { always_quote => 1, auto_diag => 1, sep => ',', binary => 1}) or die "Error creating CSV object: ", Text::CSV->error_diag;

open(my $csvh, '>', $csvfile) or die "Error opening $csvfile: $!";



my $sth = $dbh->prepare('select * from Events');

my $res = $sth->execute;



# assign column mapping

$csv->column_names(\@out_cols);



# Print CSV header

# Commented because Anything With SMS doesn't like it

# print header to csv

# $csv->print($csvh, \@out_cols);

# $csvh->print("\n");



my $count = 0;

while (my $in = $sth->fetchrow_hashref) {

        next unless $in->{'service_id'} eq '3';

        $count++;

        my $out;

        

        # convert some fields for csv output

        $out->{'type'} = 'sms';

        

        $out->{'x'} = '';

        # flags

        my @folders;

        if ($in->{'outgoing'}) {

                if ($in->{'flags'} eq '0') {

                        push(@folders, 'sent');

                } elsif ($in->{'flags'} eq '5') {

                        push(@folders, 'submit');

                } else {

                        push(@folders, 'outgoing');

                }

        } else {

                if ($in->{'is_read'} eq '1') {

                        push(@folders, 'read');

                } else {

                        push(@folders, 'read');

                }

        }

        $out->{'folders'} = join(',', @folders);

        $out->{'number'} = $in->{'remote_uid'};

        $out->{'msg'} = $in->{'free_text'};

        

        # Anything with SMS doesn't support escaped quotes or newlines in CSV files

        # Replace double quotes with singles, and newlines with spaces

        if (defined $out->{'msg'}) {

                $out->{'msg'} =~ s{"}{'};

                $out->{'msg'} =~ s{\n}{ };

        }

        $out->{'time'} = strftime('%Y-%m-%d %H:%M:%S', localtime($in->{'start_time'}));

        

        $csv->print_hr($csvh, $out);

        $csvh->print("\n");

}

$dbh->disconnect;

close($csvh);

print "Exported $count messages\n";

exit(0);

All times are GMT. The time now is 00:08.

Page 1 of 2

1

Show 40 post(s) from this thread on one page

vBulletin® Version 3.8.8