How to export SMS and MMS messages from Sailfish OS to PC/Excel

Jolla Care -

This article explains how to copy the text and multimedia messages from the SQLite database in Sailfish OS to CSV files. Note: the attachments of MMS messages must be handled separately - they will not be included in CSV.

  • Enable Developer Mode
  • Open Terminal app on your computer and create an SSH connection to your Sailfish device
    • SSH over USB from Windows to Sailfish: read this
    • SSH over USB from Ubuntu to Sailfish: read this
  • Give the following commands to access the SQL database:
cd $HOME/.local/share/commhistory
devel-su
pkcon refresh
pkcon install sqlite sqlite3 commhistory.db sqlite> .headers on sqlite> .mode csv sqlite> .output SMS.csv sqlite> select * from events where type = 2; sqlite> .output MMS.csv sqlite> select * from events where type = 6; sqlite> .quit
exit mv *.csv $HOME
  • Exit SSH and disconnect USB
  • Connect your device to PC again, now using the MTP mode
  • Move files SMS.csv and MMS.csv from your device to PC
  • Open CSV files with Excel or Libre Office. Do "Text to Columns" operation for column A using comma as the delimiter.

Column A contains now the message contents sent by you and column P has the contents received by you.

It is not handy to read the messages in Excel. It is better to copy/paste them to Word, for instance.

 

NOTE:  the date and time of the messages are given in the Unix timestamp ("Epoch format") as "seconds from 1970-01-01". They can be converted to a human-readable date/time format at https://www.unixtimestamp.com/index.php.

Example:  the value 1593074952 means 2020-06-25T08:49:12+00:00 (in ISO 8601).

It is possible to create a formula to LibreOffice (or the like) to do the conversion automatically. See the instructions at https://unix.stackexchange.com/questions/421354/convert-epoch-time-to-human-readable-in-libreoffice-calc. 

In deviation to that, Sailfish OS has those time values in seconds (not milliseconds). Hence the conversion formula is:

H3 = H2/(60*60*24)+25569, where cell H2 holds the value from Sailfish and H3 the wanted result.
Show H3 on LibreOffice using the data and time format of your liking.

 

 

 

 

 

 

 

Have more questions? Submit a request
Powered by Zendesk