How to transfer iPhone (SMS) messages to Excel

It's very easy to get your messages out of your iPhone:

1. Create a backup of your iPhone on your computer, that is _not_ encrypted (as described here)
2. Find the files of your backup:
   
   • on Windows 8.x and 10 it is here: c:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows 7 and Vista: C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows XP: C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup 
   • on OS X: ~/Library/Application Support/MobileSync/Backup/
3. Each backup name is a 40 character long SHA (seems like garbage: 00d07a612db092c28c316244cce7d9199f23da33).
   Inside of them you will find another 256 folders named 00-ff, and inside those are your files, also with the long hash (garbage) names.
4. Go into the folder '3d', find the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28
5. Copy it for example to your desktop, rename it to: 3d0d7e5fb2ce288813306e4d4636395e047a3d28.sqlite
6. Download the free and open-source SQLite database browser: http://sqlitebrowser.org/
7. Install it, start it and open the .sqlite file from your desktop.
8. Go to the Execute SQL tab, and enter:
   select  chat.last_addressed_handle,
           chat.chat_identifier,
           datetime(message.date + strftime('%s', '2001-01-01 00:00:00'),'unixepoch', 'localtime') as date,
           message.text,
           message.service
   from chat, chat_message_join,message
   where chat_message_join.chat_id=chat.ROWID and chat_message_join.message_id=message.ROWID
   order by last_addressed_handle,chat_identifier,date;
9. Now you should get a table with 5 columns (from which phone, to which number, date, text and service) and as many rows as many messages you have saved in the backup!
10. Save them as CSV:
    
11. Now you can open that with Excel!

Extra step, if you have messages with international characters: you need to add three bytes (the UTF8-BOM) to the beginning of the file for Excel to recognize this:

• You can either open the file with Notepad++ and save it as UTF-8-BOM as you see it in the screenshot

• Or if you have bash, use this command line to add it:
  echo -ne "\xEF\xBB\xBF"|cat - iphone-messages.csv > iphone-messages-BOM.csv

 Enjoy!

Feel free to modify or extend the SQL to suit your needs, please post in the comments if you have a cool new one!