Skip to content

Instantly share code, notes, and snippets.

@MatthewSteeples
Created June 19, 2013 15:49
Show Gist options
  • Save MatthewSteeples/5815391 to your computer and use it in GitHub Desktop.
Save MatthewSteeples/5815391 to your computer and use it in GitHub Desktop.
Pull Skype chat history out into XML and then query it using LinqPad
var xml = XElement.Load(@"C:\users\matthew\desktop\query.xml");
var names = new string[] { "" }; //Full names of participants to look for
var messages = xml.Elements("Table")
.Where(a => names.Any(a.Element("participants").Value.Split(' ').Contains))
.Where(a => a.Element("body_xml") != null)
.Where(a => a.Element("author") != null)
.Select(a => new
{
From = a.Element("author").Value,
Msg = a.Element("body_xml").Value,
Timestamp = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddSeconds(long.Parse(a.Element("timestamp").Value))
})
.OrderBy(a => a.Timestamp)
.Dump();
using (var conn = new SQLiteConnection("Data Source=C:\\users\\matthew\\desktop\\main.db; Version=3"))
{
conn.Open();
using (var comm = conn.CreateCommand())
{
comm.CommandText = "select * from Messages inner join Chats on messages.chatname = chats.name";
comm.CommandType = System.Data.CommandType.Text;
using (var adaptor = new SQLiteDataAdapter(comm))
{
using (var ds = new DataSet("Query"))
{
adaptor.Fill(ds);
ds.WriteXml("C:\\users\\matthew\\desktop\\Query.xml");
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment