Transferring messages to android from a sqlite db on my laptop

Question

I am a new android user. I switched from nokia to android today only. Been googling around for various solutions to transfer my sms (text messages only) from my old nokia to my new Android (Galaxy Ace with Android 2.3).

After exploring my laptop, I found the synced(using Nokia PC Suite) sms were stored in AppData as a sqlite file.

Can I transfer all these to my new Android? Any app for this?

Answer 1

Well I solved the problem. Documenting the solution here for future references.

Using any sqlite manager (I used firefox plugin- SQlite Manager), export the database to XML format (generally any db manager has options to export the database in csv, xls & xml formats).

If your sqlite was generated by nokia pc-suite sync, you would get an xml like:

<?xml version="1.0" encoding="utf-8"?>
<!--
- sqlite-manager XML Dump
- version 0.7.1
- http://sqlite-manager.googlecode.com
-
- Generation Time: Sun, 08 Jul 2012 14:12:42 GMT
- SQLite version: 3.7.10
-->

<!-- Database: msg_db.sqlite -->
<sm_xml_export version="2.0">
  <database name="msg_db.sqlite">
    <table name="messages">
      <column name="msg_txt" type="3">Your message body</column>
      <column name="msg_address" type="3">+919203487229</column>
      <column name="msg_folder" type="1">1</column>
      <column name="msg_time" type="1">1305483332</column>
      <column name="msg_imei" type="3">355940045825435</column>
      <column name="msg_status" type="1">36</column>
      <column name="msg_uid" type="3">{c33eb602-1456-4542-a755-268dc3728638}</column>
      <column name="msg_address_substr" type="3">4487229</column>
      <column name="msg_subject" type="3"/>
      <column name="msg_attachments" type="3"/>
      <column name="msg_type" type="1">0</column>
      <column name="msg_binary_path" type="3"/>
      <column name="msg_attachment_tns" type="3"/>
      <column name="msg_sent_frompc" type="1">0</column>
    </table>
    <table name="messages">
      <column name="msg_txt" type="3">Hi gopi, how are you? ;)</column>
      <column name="msg_address" type="3">+919582821300</column>
      <column name="msg_folder" type="1">1</column>
      <column name="msg_time" type="1">1305516490</column>
      <column name="msg_imei" type="3">355940045825435</column>
      <column name="msg_status" type="1">36</column>
      <column name="msg_uid" type="3">{15a0d257-2318-4baa-b764-dd14c1aa1fb5}</column>
      <column name="msg_address_substr" type="3">2821300</column>
      <column name="msg_subject" type="3"/>
      <column name="msg_attachments" type="3"/>
      <column name="msg_type" type="1">0</column>
      <column name="msg_binary_path" type="3"/>
      <column name="msg_attachment_tns" type="3"/>
      <column name="msg_sent_frompc" type="1">0</column>
    </table>
  </database>
</smses>

Then you can use the following php script to do the conversion to the generate the xml format script required by the SMS Backup and Restore App.

<?php

$file=fopen("sms-20120708160401.xml", "w");
$xml=simplexml_load_file("messages.xml");
echo $xml->getName()."<br/><br/>";
$begin="<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>\n";
$begin.='<?xml-stylesheet type="text/xsl" href="sms.xsl"?>'."\n";
$begin.='<smses count="">'."\n";
fwrite($file,$begin);
$start='  <sms protocol="0" ';
$finish='/>';
$i=1;
$j=0;

foreach($xml->children()->children() as $table) {
    echo $i++."  ";
    $read=1;
    $msg_status=$table->column[5];
    $msg_folder=$table->column[2];
    if($msg_status=="34") {
        $type=1;
        $read=0;
    }
    else if($msg_status=="36") {
        if($msg_folder=="4294967295") {
            $type=3;
        }
        else {
            $type=1;
        }
    }
    else if($msg_status=="1" || $msg_status=="5") {
        if($msg_folder=="4294967295") {
            $type=3;
        }
        else {
            $type=2;
        }
    }
    else {
        echo "<br/>".$msg_status."; ".$msg_folder."<br/><br/>";
    }
    $body=htmlspecialchars($table->column[0]);
    $body=str_replace("\n", '&#10;', $body);
    $address=$table->column[1];
    if(strlen($address)==11) {
        $address="+91".substr($address,1);
    }
    else if(strlen($address)==10) {
        $address="+91".$address;
    }
    else if($address=="") {
        $i--;
        continue;
    }

    $sms=$start;
    $sms.='address="'.$address.'" ';
    $sms.='date="'.$table->column[3].'000" ';
    $sms.='type="'.$type.'" ';
    $sms.='subject="null" ';
    $sms.='body="'.$body.'" ';
    $sms.='toa="null" ';
    $sms.='sc_toa="null" ';
    $sms.='service_center="null" ';
    $sms.='read="'.$read.'" ';
    $sms.='status="-1" ';
    $sms.='locked="0" ';
    $sms.='date_sent="null" ';
    $sms.=$finish."\n";

    fwrite($file, $sms);
}

$end='</smses>';
fwrite($file, $end);
fclose($file);

?>

One point to be noted:

You have to manually write the count attribute in the smses tag. Just check the number of lines in your generated xml file & subtract 4 from it OR just write the last echoed integer by the script

Now just send this to your android & restore using the SMS Backup & Restore App. Bingo! you are done!

Answer 2

It's not as simple as to copy the SQLite database to your new Android device, as the internal structures will differ (they even do between Android versions). There are a lot of apps in the play store to Backup/Restore your SMS on Android, using defined API-calls to extract / restore messages from/to your Android device. But unfortunately I saw none capable of converting from other formats. As some of them store the SMS-Backup in XML Format, you might be able to manually produce such a XML file for import (though I would think that being to much overhead).