How to export Iphone SMS to Excel spreadsheet

Step 1 – Do a Full backup of the iphone :

• Make sure to do a sync/backup of your iPhone in iTunes before starting to ensure all messages will be extracted.

Step 2 – Download SQLite:

• Now download SQL lite database Browser

• SQLite Database browser is a light GUI editor for SQLite databases, built on top of QT. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface.

You can download the FREE version of SQLite from here

http://sourceforge.net/projects/sqlitebrowser/

Step 3 – Find the SMS database File:

• The iphone SMS messages are stored in an SQLite database format.

• The file  is located in a backup folder on your computer. The file is named

3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata

• In Windows 7/Vista the file is stored in this path

C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\(some random id)\

• In Windows XP the file is stored in this path

C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup\(some random id)\

• Copy 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file from the folder

Step 4 – Save the Iphone SMS db file as SQLite file:

• Save the 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file in the desktop.Rename the file as sms.sqlite

Step 5 – Opening the SMS SQLite Database

• Open the downloaded sqlitebrowser-1.3-win.zip file
• Extract to a folder
• Now open SQLite Database Browser.exe

• Click File>>Open Database>>Open sms.sqlite from the desktop
• Now click File>>Export>>Table as CSV file
• Select table name as message from the Drop down>>Select export

• Save the CSV File as Sms.csv

Step 6 – Exporting to Excel

• Open the Sms.Csv file in Microsoft Excel and Save the file as Sms.xls
• Open the SMS.XLS file.

Here are a few fields that you are interested in:

1. address: This holds the phone number of the person that sent you or you sent the message to.
2. date: This is a Unix timestamp of when the message was sent.
3. text: The actual message.
4. flags: This should be either 2 or 3. The messages flagged 3 are messages that you sent (outgoing), while the messages flagged 2 are incoming messages.

Format the date column by inserting a new column after the date column and insert this formula

=(((C2/60)/60)/24)+DATE(1970,1,1)+(-5/24).Now drag the formula for all cells .

Select all cells now & right click select Format all cells>>Number>>Custom>>MM/DD/YYYY

Hurray That’s it! I hope this will be useful to someone, and if you have any questions or comments, or find any errors in this post just leave a comment!