Export iPhone SMS to the Excel worksheet

Posted on January 19, 2011

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.

Export iPhone SMSHow to export iPhone SMS to Excel spreadsheetDownload 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 startingNow find SMS database file 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)\

Export iPhone SMS Excel

NOTE – Generally the iPhone messages are stored in an SQLite database formatNow Copy 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file from the folderNow Save iPhone SMS db file as SQLite file and to do that, Save 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata in desktop and rename it to sms.sqliteNow Open downloaded SQLite zip file and extract SQLite Database Browser.exe and Run it

Export iPhone SMSNow Click File menu >> Open Database>> and Open sms.sqlite file from the desktopNow click File >> Export>> Table as CSV fileSelect table name as message from the Drop down>>Select exportAnd Save the CSV File as Sms.csv

Export iPhone SMS

Now Simply Open the Sms.Csv file in Microsoft Excel and Save it as the file name as Sms.xlsNow Open the Sms.xls file and see few fields that you are interested in -address: This holds the phone number of the person that sent you or you sent the message to.date: This is a Unix timestamp of when the message was sent.text: The actual message.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.

Export iPhone SMSNow 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 cellsNow select all cells and right click select Format all cells>>Number>>Custom>>MM/DD/YYYY as shown image bellow

Export iPhone SMS Excel

Export iPhone SMSAnd 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

Text Message For Free on iPhone & iPod Touch and iPad

Posted on December 06, 2010

Text Message For Free on iPhone & iPod Touch and iPad