Export iPhone SMS to Excel Spreadsheet

Folks, today I am going to share a trick with you, The trick is – How to Export iPhone SMS to Excel spreadsheet. This trick is quite simple though and you need not to be a technical person to do this. Just follow the guidelines from bellow to export iPhone SMS to Excel spreadsheet.

How to export iPhone SMS to Excel spreadsheet

• Download a software called – SQLite (a Database browser)  from here

Well, 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.

• Now backup your iPhone information in iTunes before starting
• Now find SMS database file located in a backup folder on your computer. The file is named –3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata

1. - In Windows 7/ Vista the file is stored in this path - C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\(some random id)\
2. _ In Windows XP the file is stored in this path - C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup\(some random id)\

NOTE – Generally the iPhone messages are stored in an SQLite database format

• Now Copy 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file from the folder
• Now Save iPhone SMS db file as SQLite file and to do that, Save3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata in desktop and rename it tosms.sqlite
• Now Open downloaded SQLite zip file and extract SQLite Database Browser.exe and Run it

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

• Now Simply Open the Sms.Csv file in Microsoft Excel and Save it as the file name as Sms.xls
• Now Open the Sms.xls file and see 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.

• Now insert a new column after the date column and insert this formula

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

• Now select all cells and right click select Format all cells>>Number>>Custom>>MM/DD/YYYY as shown image bellow

• And that’s it. You have simply done it.

Well, Stay tuned with us for more updates like this, and if you have any questions or comments, or find any errors in the process just leave a comment!