Thursday, July 11, 2013

Exporting Skype Chat/Skype Contacts to csv file using the shell script and sqlite3 (usually already installed on mac)

Edit: a very first draft version of Skype Exporter have been made and released.
https://github.com/renesto/SkypeExportSwift/releases
check it, and report any issues to renesto-at-gmail.com

I heard there are not so many nice ways to get the chat history from skype, so I came across a page of one guy who shows a way to open the sql chat history database and delete all conversations. I thought to make something more from that idea (as delete chat history is now one of the options in skype)

Create a file exportFromSkype.sh
place either this text:
sqlite3 -batch "$HOME/Library/Application Support/Skype/$1/main.db" <<EOF
.mode csv
.output $2.csv
select * from Messages where dialog_partner = '$2';
.output stdout
.exit
EOF
now make the script executable by running

chmod +x exportFromSkype.sh
now, when you want to export some chat history from certain person, you can do it by:

sh exportFromSkype.sh your_skype_name your_friends_skype_name

and it will create a csv file with name persons_skype_name.csv in the same folder where the script is placed.

You can also modify the script, and use for example as a sql query other tables, like:

sqlite3 -batch "$HOME/Library/Application Support/Skype/$1/main.db" <<EOF
.mode csv
.output contacts.csv

select * from Contacts;
.output stdout
.exit
EOF
 
 This will give you whole list of Contacts in csv file. when you run it as:
sh exportFromSkype.sh your_skype_name

A bit more step-by-step instruction I wrote in this post

Edit: In case you want to get the exact time-date export, I suggest changing the "select ..." line with the following one:
select author,dialog_partner,datetime(timestamp, 'unixepoch', 'localtime'),body_xml from Messages where dialog_partner = '$2';
Edit: Edit: Thanks to Bob for discovering incomplete query, I tried to fix it now.
select author, datetime(timestamp, 'unixepoch', 'localtime'), edited_timestamp, body_xml from Messages where convo_id in (select id from Conversations where identity = '$2');