Exporting skype conversations/chat logs on OSX
29 Aug 2014** Update Feb 22th 2015 : Translate unix timestamps into local time
Today I wanted to export my Skype chat logs but I could not find a good way other than copy and pasting it page by page.
After a little googling it turned out, that Skype is using SQLite as a storage engine so it is as simple as finding the main database and then load it up into SQLite.
TLDR; Get all messages
On OSX you need 3 things:
- A Terminal
- Your skype username
- SQLite
While I'm comfortable doing this all on the terminal, you can also use any of the SQLite GUIs out there.
Then do the following:
- Quit Skype, otherwhise the database will be locked
- Open up your Terminal
- Type in the following and do not forget to replace
your_skype_usernamewith your actual skype name
sqlite3 ~/Library/Application\ Support/Skype/your_skype_username/main.db "select author, datetime(timestamp, 'unixepoch', 'localtime'), body_xml from messages;" > ~/Downloads/skype_chat_history.txtHat tip to Jako for giving me the inspiration
Poking around
Since it is a regular SQLite database you can load it up into an interactive shell and look around a little bit.
$: sqlite3 ~/Library/Application\ Support/Skype/your_skype_username/main.db
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>Quitting the shell
sqlite>.quitListing the tables
sqlite> .tables
Accounts ChatMembers DbMeta SMSes
Alerts Chats LegacyMessages Transfers
AppSchemaVersion ContactGroups MediaDocuments VideoMessages
CallMembers Contacts Messages Videos
Calls Conversations Participants VoicemailsShow table definition
So we can see what the columns mean
sqlite> PRAGMA table_info(Messages);
0|id|INTEGER|1||1
1|is_permanent|INTEGER|0||0
2|convo_id|INTEGER|0||0
3|chatname|TEXT|0||0
4|author|TEXT|0||0
5|from_dispname|TEXT|0||0
6|author_was_live|INTEGER|0||0
7|guid|BLOB|0||0
8|dialog_partner|TEXT|0||0
9|timestamp|INTEGER|0||0
10|type|INTEGER|0||0
11|sending_status|INTEGER|0||0
12|consumption_status|INTEGER|0||0
13|edited_by|TEXT|0||0
14|edited_timestamp|INTEGER|0||0
15|param_key|INTEGER|0||0
16|param_value|INTEGER|0||0
17|body_xml|TEXT|0||0
18|identities|TEXT|0||0
19|reason|TEXT|0||0
20|leavereason|INTEGER|0||0
21|participant_count|INTEGER|0||0
22|error_code|INTEGER|0||0
23|chatmsg_type|INTEGER|0||0
24|chatmsg_status|INTEGER|0||0
25|body_is_rawxml|INTEGER|0||0
26|oldoptions|INTEGER|0||0
27|newoptions|INTEGER|0||0
28|newrole|INTEGER|0||0
29|pk_id|INTEGER|0||0
30|crc|INTEGER|0||0
31|remote_id|INTEGER|0||0
32|call_guid|TEXT|0||0
33|extprop_chatmsg_ft_index_timestamp|INTEGER|0||0
34|extprop_chatmsg_is_pending|INTEGER|0||0Listing all skype users I have ever exchanged messages with
sqlite> select dialog_partner from messages group by dialog_partner;
user1
user2
user3Listing the conversation with a specific skype user
Let's say I was talking to peterpan for the last year or so and want to see all messages:
sqlite> select author, datetime(timestamp, 'unixepoch', 'localtime'), body_xml from messages where dialog_partner = 'peterpan';Listing all chats
sqlite> select name, friendlyname, topic from chats;
#a_redacted_skype_username/$a_redacted_skype_username;a_redacted_id|Peter Pan, Michael Jordan, Hans Foo|Chat TopicListing all messages from a specific chat
We could do this in one SQL statement but for illustration purposes, I'm splitting it up into statements.
First we need to find the chat id of the conversation we are interested in:
sqlite> select name from chats where topic = 'Name of the Chat';
#a_skype_user_name/$another_skype_username;aa0aaaa0a000aa00Then we can use this value to find list all the messages from this conversation:
sqlite> select author, datetime(timestamp, 'unixepoch', 'localtime'), body from messages where chatname = '#a_skype_user_name/$another_skype_username;aa0aaaa0a000aa00';Exporting a result to a CSV file
sqlite> .mode csv
sqlite> .output /tmp/my_result.csv
sqlite> select * from messages;
sqlite> .quit$: open /tmp/my_result.csvClosing thoughts
Judging by the tables, there are a lot more things to find in the database:
sqlite> .tables
Accounts ChatMembers DbMeta SMSes
Alerts Chats LegacyMessages Transfers
AppSchemaVersion ContactGroups MediaDocuments VideoMessages
CallMembers Contacts Messages Videos
Calls Conversations Participants VoicemailsAt this point I am not sure why there is a table called Conversations, I can only assume that it is the parent table for Calls, SMSes, VideoMessages, Messages etc.
