macOS AppleScript to extract SMS as text from iPhone backup folder

[yakyu]

Wrote a script to extract SMS as delimited text over the weekend. Just save the code to a file with ".scpt" extension and download the sqlite3 Mac OS X binary from the link in the comment to the same folder and you are set to go.

Feel free to share and copy. An acknowledgement will be appreciated.

(*
Copyright: Alan Yeung 2010 alankiyeung@me.com

Script Name: SMSfromiPhone

Purpose: AppleScript to extracts SMS from iPhone backup database file in SQLite format. Output is a text file in the format of delimiter (user-defined) separated values.

Note: Requires "sqlite3" binary for Mac OS X to exist in the same library to run. For download, please goto: http://www.sqlite.org/download.html
*)

property smsDBName : "3d0d7e5fb2ce288813306e4d4636395e047a3d28"

-- save text item delimiters
set savedDelimiters to AppleScript's text item delimiters
set AppleScript's text item delimiters to {":"}

-- set up current (working) directory (i.e. where this script and sqlite3 reside)
set currPathToMe to path to me as text
set textList to text items of currPathToMe
set pathDepth to count of textList
set currPathText to text 1 thru (pathDepth - 1) of textList
set myName to text pathDepth of textList
set currPath to currPathText as text

-- set up for iPhone backup direcotry
set defaultFolderText to ((path to home folder) as text) & "Library:Application Support:MobileSync:Backup"
try
set defaultFolder to defaultFolderText as alias
on error number -43
set defaultFolder to path to home folder as alias
end try

-- properly set up path to dbFile
set dbFileText to ""
repeat while dbFileText = ""
try
set backUpFolder to choose folder with prompt "Please choose the iPhone Backup folder containing the file:" & smsDBName default location defaultFolder
on error number -128
display dialog "Action Cancelled." buttons {"OK"} with title myName
return
end try
set dbFileText to (backUpFolder as text) & smsDBName
try
set dbFile to dbFileText as alias
on error number -43
set dbFileText to ""
end try
end repeat

-- set up separator
set sepText to ""
repeat while sepText = ""
set dialogResult to display dialog "Separator: (cannot be blank)" default answer "|" buttons {"OK"}
set sepText to text returned of dialogResult
end repeat

-- set up output directory
try
set outFile to choose file name with prompt "Save output as:" default name "output.txt" default location (path to home folder)
on error number -128
display dialog "Action Cancelled." buttons {"OK"} with title myName
return
end try

-- execute shell command
do shell script POSIX path of currPath & "/sqlite3 -header -separator '" & sepText & "' \"" & POSIX path of dbFile & "\" \"SELECT * FROM message;\" > " & POSIX path of outFile

-- restore text item delimiters
set AppleScript's text item delimiters to savedDelimiters

 

[dgtheriault]

hi yakyu,
the script sounds like exactly what I need - thanks for that.
But when I try it, all the data files in the Backup directory are grey'ed out, not possible to select them. I've tried unlocking the Backup folder and checking permissions, it didn't seem to make a difference.
cheers,
dgt