07-13-10

Convert iPhone sms.db to txt (csv or excel) via command line

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 sms.d
.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!

Via: http://preda.wordpress.com/2008/06/22/move-your-sms-from-iphone/