Getting Your Groups out of TweetDeck (Update)

[ 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:

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