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

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)

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

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.

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

Re: Export SMS from N900 to Android

Quote:

Originally Posted by BabylonV2000 (Post 647921)

did you export sms from smsexporter?

Cheers

Re: Export SMS from N900 to Android

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

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.

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

}

Re: Export SMS from N900 to Android

Quote:

Originally Posted by phip (Post 1419927)

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.

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

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

Re: Export SMS from N900 to Android

Quote:

Originally Posted by lameventanas (Post 1542186)

In case anyone is still migrating away from Maemo

I just had to use it. :'(

Thanks a lot for sharing! :)

Re: Export SMS from N900 to Android

Quote:

Originally Posted by helex (Post 1545515)

I just had to use it. :'(

Thanks a lot for sharing! :)

Wow I was not the last one!

Welcome to civilization ;)

Re: Export SMS from N900 to Android

Quote:

Originally Posted by lameventanas (Post 1545516)

Wow I was not the last one!

Welcome to civilization ;)

WUT?
You mean welcome to mainstream walled garden?

SMS is still working fine on my N900!

Re: Export SMS from N900 to Android

Thanks for this script! I just migrated from n900 to Android 9. On Ubuntu 18.04 I needed to install

Code:

sudo apt install libtext-csv-perl libdbd-sqlite3-perl

for the script to work.

Since Anything with SMS is not available anymore from Google Play I installed it from apkmonk. First I tried the autodetected "Message Xtreme SMSGlobal", but the order of fields was wrong and I had to delete over 15k messages - since the shitty Android Messages app doesn't offer this (because it's dangerous m'kay), I used ChompSMS. Finally, I gave up with predefined formats and used the following:

Code:

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

and defined "read" keyword for inbox and "sent" for sent.

Re: Export SMS from N900 to Android

i am just migrating all SMS from my >10y old N900 to my brandnew FxTec Pro1 - with physical QWERTY keyboard - running Android 9. i managed converting the el-v1.db file to a CSV file. now it's all about getting this el-v1.csv into the Pro1...will see how it works with ChompSMS. does anybody have some detailed instructions for that ? i saw ThePainoGuy's note - thanks much for this starter !

i am also curious how to get my old addressbook into the Pro1. any experience out there ?

Re: Export SMS from N900 to Android

i am a bit lost with this Anything with SMS app for my Android phone. is there somewhere an instruction how to use this thing ? i saw that one can change formats and make patterns - still, the whole workflow is pretty unclear to me currently.

a further problem is how and where to transfer by sms.csv file from my linux box to the Android phone ? jmtpfs with PTP puts the file into Images/Pictures but i don't see the file with in Android (only photos). is there a way to put it into Downloads, in case that helps to see the file as a CSV file in Android ?

last : the addressbook transfer was ultimately quite a simple task and is done.