Exporting Data from CalorieKing

I got this software called CalorieKing. It helps me keep a food diary, track my weight loss, and all that sort of thing. Pretty handy. It’s data export features are really limited. They’re far less limited if you know how to run a few SQL queries against its database.

The software will let you track things like weight, waist, thigh, hips, and chest measurements. It will even plot your weight as a graph. As far as I can tell, it won’t plot your waist or other measurements, though. Silly. Here’s the data it’s tracking:

CalorieKingList

I was able to export the measurements to a CSV format, which I can then plot with Excel, through the following steps. These are not for the faint of heart, and it’s only because I’m such a consummate geek that I went to this kind of trouble.

1. Export from CalorieKing to a backup DB

This is as simple (on MacOS X) as going to File -> Backup Data. You have to tell it where to back up. That’s in your preferences. In my case, it went to ~/Documents/CalorieKing/20070203.ddBackup.

2. Compile and Install SQLite 2.8

The guys from CalorieKing are using an old version of SQLite. It turns out that MacOS X includes SQLite by default, but it’s version 3.1.3, and that doesn’t seem to grok the version 2.1 database that CalorieKing uses.

I followed the CVS update instructions and got version 2.8 from CVS. Then I built it following your typical build instructions:

  1. ./configure —prefix=/sw
  2. make
  3. make install

Run A Query

This query will pull the measurements out as a CSV file.

./sqlite -separator , ../20070203.ddBackup "select date,weight,chest,waist,hip,thigh from user_checkins ;"

The output corresponding to the above table looks like this:

20070103,95.2997665405273,104.139999389648,111.76000213623,114.300003051758,66.0400009155273
20070110,94.1204223632812,105.410003662109,111.76000213623,111.76000213623,66.0400009155273
20070117,93.2132415771484,104.139999389648,111.125,111.76000213623,66.0400009155273
20070124,93.4400329589844,100.330001831055,110.48999786377,111.76000213623,66.0400009155273
20070203,92.5328521728516,104.139999389648,108.584999084473,108.584999084473,

Redirect that to a file and you’ve got data. Notice that for inches I have “44” in the table above, but this output shows “111.76000213623”. It appears to be using some outrageously large floating point data type to store the value in centimeters, not inches. I’ll have to convert that to inches if I want to graph my measurements in a familiar form.