Home » iphone » Move your SMS from iPhone to Excel/ascii delimited

Move your SMS from iPhone to Excel/ascii delimited

There seems to be a lot of applications to export SMS out from the iPhone, on a .txt file or Excel, under Windows XP/Vista. I tried a lot of them but none was working in my iPhone 1.1.4: iDatabackup was crashing on startup, Tansee demo version did not recognize any sms on my iPhone.

I found the way to do manually in a simple and secure way.

This is a simple step-by-step procedure and shall be used on a jailbroken iPhone; if you have a virgin one I think there is a tool to extract sms.db out of iTunes backup, when you have this file you can go on from Step 2.

Step 1 - transfer the file sms.db in your PC:

This step is quite easy but you have to install on your PC the program WinSCP or any other Secure FTP (SFTP) program. Some SSH clients have this addon application. You have to connect to your iphone via Wi-Fi, so your PC and the iPhone have to be on the same area, connected on the same network.

To discover the ip address you can go in general settings / network / wifi proprerties, or you can run Terminal on you iPhone (if you have installed this application) and run ifconfig, the address is the one near “inet” word.

You have to find the file Llibrary/SMS.app/sms.db or /var/mobile/Library/SMS.app/sms.db and copy it somewhere in your PC.

Step 2 – convert from sms.db to out.txt (ascii comma-delimited text file)

To do this conversion you have to download in the above directory the SQLite3 client from here.

Then open DOS Prompt, go in the directory where you placed sqlite3.exe and sms.db and type exatly this text (don’t forget the semicolon after message). Mind that this is case-sensitive!

sqlite3.exe sms.db
.output out.txt
SELECT * FROM message;
.quit

That’s all: in your out.txt file you have your sms, in a readable format.

Step 3 – Import your sms in an Excel / spreadsheet, useful to convert date/time field in an human readable format

Run Excel, open the file out.txt. Excel will ask you if it’s a delimited file or a fixed length. Chose delimited and insert a “pipe” symbol ( | ) in the “custom” separator at the end of the list.

Click ok and your file should be correctly imported.

Add a new column after column C.

In D1 cell (should be empty as you added freshly) cut&paste this text:

=C1/86400+25569+(0,04166*(2-G1))

don’t forget “=” at the beginning of the string. Format this cell as custom, typing: dd/mm/yyyy h.mm.ss (for italian users: gg/mm/aaaa h.mm.ss

You should read the converted date&time. Now click on this D1 cell, type CTRL+C then select the D1 column and type CTRL+V to spread this conversion for all your SMSes.

In column F, 2 means Received, 3 means Sent, 0 means Unread.

Column G: contains the clock correction in hours, this is used in D formula so don’t remove this column.

If you discover the meaning of the other columns, let me know, thanks!

About these ads

20 Comments

  1. sparsham says:

    This is absolutely cool… what else can you expect.

  2. dam says:

    Hi:
    i backed up my sms from my iphone to a “text file” with phoneview (wrong idea)!
    now, i have the 3.0 SW and i want to recover those messages!
    ther is a way to do that??

    thank you very much!

  3. hajime says:

    I don’t seem to be able to use the following syntax on my excel spreadsheet (Excel 2003)

    =C1/86400+25569+(0,04166*(2-G1))

    Kindly assist.

    • Emanuele Preda says:

      because in italian we use “,” instead of “.” for decimals.

      Try this:

      =C1/86400+25569+(0.04166*(2-G1))

  4. Troy says:

    Thank you for the information on the SMS DB. In America, for proper formatting, please note that your code: “=C1/86400+25569+(0,04166*(2-G1))” must be updated to reflect our decimal seperator “=C1/86400+25569+(0.04166*(2-G1))” and that our date format would be mm/dd/yyyy h:mm:ss.

    But… I still thank you for taking care of the hard part! I appreciate your write up!

  5. danish says:

    great great work btw very useful and saves us from havign to upload to strange websites for this thank u :)

    any idea about the phonenumbers that the texts are sent to because i have all teh texts but dont know whos sent them?

    thanks in advance :)

  6. Safin says:

    any ideas how to extract out mms?

  7. andyk says:

    loads of help — your a life saver — i really needed those sms(s) and the dates !!! thanks

  8. TomTom says:

    If you are looking for a tool to view and export your contacts, text messages (sms) your calendar entries and more then check out ‘iTwin’ from here: http://www.i-twin.de/en/ A neat PC program with many features, that Apple should have already included into iTunes but never did. Rock stable, easy and a ‘must have’. Cheers …

  9. mohammed says:

    wow this was so helpful, its really cool. iv got a question how did you guys figure out that fourmula for the date it seems really complecated, im intrested on knowing how… thanks

    • mohammed says:

      sorry iv got another question, does this work for deleted texts as well coz if it does that would be sweeeeeeeeeeeeeeeeet

  10. Marc says:

    Cool, but a strange thing happens. 1265117299 converts into 03/02/2014 15.28.18

    I know that the message was actually send on 02/02/2010

    The value in the G column (fieldname is ‘replace’) is zero

    Do you have any idea what happens here?

    Greetings!

  11. Yves says:

    Exceptional job ! Thanks a lot

  12. Muaad says:

    This is absolutely cool… what else can you expect.

    but how can add sms from csv into SMS.db file again

    Thank you so much

  13. Akshay Thakker says:

    This is great. Is there a way for me to convert a data base file exported from blackberry to the iphone sms format so i can copy it to iphone.

  14. Mitzpieler says:

    It appears the sms.db file layout has changed with IOS6. Do you have a new method of extracting? There is no longer a column displaying who the text was sent to/received from.

  15. CoDe-BuSTeR says:

    handle_id reflects a numer corresponding to a certain contact… but didn’t find the connection to the name yet.

  16. Banz says:

    Hi, great work here ! Thanks a lot !
    Howver, I wasn’t happy with the step 3, so I made a Perl script to convert the out.txt output into a HTML file, using the information in step 3.
    I give you the script here in case you’re interested, but be aware that the script converts it into a french file, which means the comments are in french, the date/time is in french, and the phone numbers are converted into french numbers… I’m really sorry for not translating it for other languages, but I’m too lazy now to modify my code…
    Anyway, I guess it can still interest some people, that’s why I share it with you anyway. If you’re motivated and you want to translate it into any other language, feel free to do so !
    Here is the script : http://pastebin.com/jKfP4gFV

    NB : the script has a problem when your SMS ends with “{some stuff} {new line} {some stuff} {a number}” (the last number is considered as a new SMS); to prevent that, you need to manually change the out.txt file to add a character after this number so it doesn’t look like this : “{number}|{….}”, a dot or a space before | works perfectly fine : “{number} |{……}”
    NB 2 : I really don’t know what happens if there is a |in one of your SMS
    NB 3 : this script has been used with SMS extracted from iOS 4.2, I don’t know if it still works with later versions of iOS
    NB 4 : I use the DateTime module

    That’s it, hope you’ll find a use for the script. And thanks again for this article !

  17. I used a more direct way described here:

    http://www.copytrans.net/support/how-to-view-iphone-sms-in-excel/

    It involves a paid third party PC app which directly exports your iphone text messages to the computer as Excel files. I didn’t pay as I used the trial which allowed export of some 200 messages – for me it was plenty.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: