29 May 2009

Getting Your Groups out of TweetDeck (Update)

Posted by khk

[ Due to a huge amount of comment spam, comments on this post are now disabled ]

No, I don’t want to get groups removed from TweetDeck, I want to extract the group information so that I can use it otherwise. By extracting group information, I mean to create a list of groups and users belonging to those groups.

You may want to do that just so that you have a backup outside of TweetDeck, or to configure another Twitter client with the same groups, or just because you can 🙂

Background

Fortunately we already have a theory of how TweetDeck stores data: It’s an Adobe AIR application, and any AIR application can store data in a SQLite database. So the assumption is that all configuration data (users, groups, searches) is stored in such a SQLite DB. We can easily verify that by looking (I’m using a Mac for that, if you are on a Windows system, I’ll get to that soon) in the TweetDeck preferences directory – on a Mac that is “Library/Preferences/TweetDeckFast.[random letter and numbers]/Local Store”. In that directory we find a file named td_26_[username].db – Bingo! That’s our database file, and a quick check with the “file” command actually confirms that:

file td_26_user.db
td_26_user.db: SQLite database (Version 3)

So, how do we get information out of that file? On the Mac we are again lucky: The OS already provides a tool to open SQLite databases: sqlite3

The following command will dump the complete content of the database (in form of SQL statements) to the terminal:

echo .dump | sqlite3 ./td_26_user.db

With a little bit of SQL archeology, it’s pretty straight forward to figure out how the data is stored.

Extracting the Data

Because most of my audience probably is not trying to do this on a Mac, I will now switch my instructions to Windows. If you need more information about how to do this on a Mac, please let me know in the comments.

The process requires a little bit of post procesing (e.g. in Excel), but once you are done, you will have access to the data.

On the Mac, the sqlite3 application actually comes with the operating system, for a Windows environment however, you need to download this file:

http://www.sqlite.org/sqlite-3_6_14_1.zip

Unzip the file and put the sqlite3.exe executable somewhere you can easily access it (e.g. I have a c:\temp directory, the path is short, easy to remember and easy to type. If you want to use this tool more often, you may want to store in in a directory where the operating system looks for executables.

Now you have to find out where the TweetDeck config files are stored. On my Windows system (I’m still running XP) it’s “C:\Documents and Settings\[user]\Application Data\TweetDeckFast.[random string]\Local Store”

You should find a file that starts with td_26 in that directory – that’s the TweetDeck database.

Now start a command tool and change to the directory that contains the td_26… file. In that directory create a new text file named sql.txt with the following content:

SELECT columns.cName, friends.fName, friends.fScreenName
FROM friends
JOIN groups ON friends.fUserID = groups.gUserID
JOIN columns ON groups.gCID = columns.cID
GROUP BY friends.fScreenName
ORDER BY columns.cName;

We are almost there…

Now go back to the command tool and run the following command

type sql.txt | c:\temp\sqlite3.exe td_26*.db

On a Mac that would be
cat sql.txt | sqlite3 td26*.db

This will print all the users who are in groups – you probably want that in a file so that you can import it into Excel:
type sql.txt | c:\temp\sqlite3.exe td_26*.db > group_data.txt

That’s it. Import it into Excel (make sure that you select “|” as field delimiter character) and do whatever you want to do with your group data.

Update (06/06/2009): Kathy Gill over at WiredPen.com has collected some information about where and how to get access to the TweetDeck database file on different systems. On Windows, you have to do a few more things to find the file:
How To: Back Up TweetDeck Groups and Preferences

Subscribe to Comments

6 Responses to “Getting Your Groups out of TweetDeck (Update)”

  1. Karl – this is very interesting.

    I wonder if you can tweak the spreadsheet and then export it so that you could update those groups without having to do the “add” or “remove” from group in the TweetDeck interface.

    It’s also interesting that “26” is the standard “number” TweetDeck is using in those filenames. I should update my post with that info.

     

    Kathy

  2. Kathy,
    unfortunately it’s not that straight forward. You need to write the right information into the right tables in the SQL database. I know how to do it, but just have not had the time to write software for it. I also don’t have a good reason anymore because I moved from TweetDeck to Nambu. Maybe one of these days…
    Karl Heinz

     

    khk

  3. […] wasn’t planned, it just happened… Let’s get back on track: As you may remember, a few days ago I posted instructions about how to extract group information from TweetDeck. Now that I’m considering ditching Nambu, I need a way to do the same with that […]

     
  4. Would love to read the instructions on how to do this on a mac.

     

    decaf

  5. Would have also loved the mac instructions…

     

    Calvin Robinson

  6. […] written a number of posts before that explained how to extract group information from TweetDeck and other Twitter applications, we can use these techniques to extract the group members and use […]