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');


19 comments:

Serj Kasparoff said...

Hi! Tell me please, is there any way to view on a mac the chat history from "chatsync" folder, that i've copied from my previous system backup without the Skype? Thanks!

renesto said...

Hej Serj. I just checked "chatsync" folder, it is different structure. I will take a look at it one time, and if I come up with something, I will keep you updated.

isabelle m said...

Thanks!
Exactly what I was looking for. Simple & efficient for automating backups.

Anonymous said...

Awesome! Thanks for sharing that info--helpful to me in migrating to pure VoIP in a GXV3140.

Oliver said...

Thank you so much, renesto, this is great! For future idiots like me, if you get 'Error: near line 3: database is locked' then just quit Skype and the database will be unlocked again - all credit for this to noneno at stackoverflow.

Dawn Moore said...
This comment has been removed by the author.
Dawn Moore said...

Hey Renesto,

Thank you for writing this and the instructions on the Skype forums. I've got all my chat histories exported to .csv files now but was wondering if you could help me with one other thing.

Looking at the files I can't identify which column is the time stamp, nor what format that time stamp is in. I wanted to figure that out so I could make a new column with the exported time stamp formatted (or converted) to date/month/year HH:MM:SS.

Any help you can offer would be most appreciated!

Thanks!
Dawn

(sorry for deleted/double post, blogger edit button eludes me if it's there.)

renesto said...

Hello Dawn,

I have updated my blog post, with the different line which would give you better generated table, with only useful columns, and also properly created date and time.

You need to replace the line that starts with "select ..." with the line I wrote at the end of the blog post.

In these Skype tables, Time is defined as a unix epoch, which counts the amount of seconds since 1970-01-01 00:00:00.

Anonymous said...

Renato,

Text Edit won't let me save the .sh file without adding .rtf on the end. I tried other formats but it won't work with them either. I can't get terminal to run it with the .rtf at the end and no matter how I try it (.sh in the command or .sh.rtf it says no directory found or some such thing. Any ideas?

renesto said...

regarding textedit. I remember having some issues way back regarding textedit behaving too smart (not showing original code, etc.), so I switched to sublime textual editor.

Can you try in TextEdit the following:
- Before you save your document, select Make Plain Text from the Format menu. (If the menu says Make Rich Text then you are already in plain text mode.)

I found this solution on a answer to question on this
forum .

Maybe this helps!

Anonymous said...

I still can't even get the chmod executable command to work. It always says "no such file or directory" when there clearly is as I have made it just like the instructions stated to.

As I can't get past this step I can't see if the text editor changes will work.

Mahesh Vaghela said...

I got error like this to export my contacts in mac.

Error: near line 1: near ".": syntax error
Error: incomplete SQL: .output stdout
.exit
EOF

Bob said...

Unfortunately, it looks like if you select by dialog_partner, you'll only get the messages you send (and not the ones they send you). But you can use those to find the convo_id, and use that to save the whole conversation.

renesto said...

Thanks Bob, I just realized your comment. I hope the new query would fix the issue you mentioned :)

whiterainbow said...

For anyone having issues with chmod, once you're sure there is no hidden rtf extension, check that the name of your file doesn't have a blank space at the end. Mine had "exportFromSkype.sh " (note the space). Once I fixed this I was able to chmod and run it just fine.

Sean F said...

I STILL can't get chmod to work!!!

I read and tried ALL the solutions to the chmod step from this thread: Sublime instead of TextEdit; no hidden rtf. extensions; no hidden spaces; etc.

Still it always says in Terminal, when I run the chmod command, "chmod: exportFromSkype.sh: No such file or directory"

HELP!

Anonymous said...

Hi am trying to extract from a Skype SQL .db a conversation between two people, I have the sql query worked out for extracting the two people I want, however when I extract the message content it includes the xml header data etc rather than just the pure text. Is there a command that strips this out?
Thanks

renesto said...

Usually, every message body that contains any kind of graphics, like smileys, emoticons etc is saved as xml. the rest of the message is usually raw. I am working now on mac app which would give you easier chance to export the messages

Daniel McClintock said...

Has anyone had any recent luck in exporting contacts for a "live:foobar" account? The folder name for that account is named "live#3afoobar", resulting in a syntax error (at least, it's my assumption that the folder name characters is the cause of this):

Error: near line 1: near ".": syntax error
Error: incomplete SQL: .output stdout
.exit
EOF