MIX 09 Videos

The MIX 09 conference is well over in the US, and all the content is now available online, all the presentations were recorded.  You can even get them all on the one page, instead of search the presentations for each day:

http://videos.visitmix.com/MIX09/All

iPod lap timer parser

The iPod Nano is a beautiful thing.  That is unless you want to do something custom.  So it is with the lap timer in it.  There is no obvious way of synchronising the timer logs with the computer.  Even if there is a way in ITunes (I’m pretty sure there isn’t), the point is moot, as I don’t use ITunes (instead I’m using the wonderful ml_ipod plugin for Winamp).  This is “suboptimal” if you want to, for example, use the lap timer to chart your jogging progress over the course of a year.  You want to crunch those stats in Excel or some such, you certainly aren’t going to copy them from the screen.

The information on the web is a little sparse on this matter, but there are a number of applications that will let you do this.  The one that caught my eye was http://wafflesoftware.net/ipodtimer/ which luckily provided source code (Mac OS X only – aren’t we feeling exclusive).  A relatively trivial exercise in parsing.  The timer format was loosely described in the TimerFormat.txt in the zipped source.  At least detailed enough for me to whip up an implementation in C#.  Hence this post.

The underlying format is a binary file.  Time to dust off .NET’s BinaryReader.  The below method ReadTimerEntries(string filepath) will read in an iPod timer file (on my Nano it’s /IPod_Control/device/timer) and produce a list containing the date of the recording, and the associated laps.  From there you have all the info you need.  Anyway, it works for me, on an iPod Nano (3rd gen).  I don’t imagine that the file structure changes much though.

1. public partial class Form1 : Form 
2. { 
3.     public Form1() 
4.     { 
5.         InitializeComponent(); 
6.     } 
7.  
8.     private void button1_Click(object sender, EventArgs e) 
9.     { 
10.         OpenFileDialog ofd = new OpenFileDialog(); 
11.         ofd.FileName = Application.StartupPath; 
12.         DialogResult dr =ofd.ShowDialog(); 
13.         if (dr == DialogResult.OK) 
14.         { 
15.             List<TimeEntry> entries = ReadTimerEntries(ofd.FileName); 
16.             foreach (TimeEntry entry in entries) 
17.             { 
18.                 System.Diagnostics.Debug.WriteLine(entry.EntryDate.ToString()); 
19.                 foreach (TimeSpan ts in entry.Laps) 
20.                 { 
21.                     System.Diagnostics.Debug.WriteLine(ts.ToString()); 
22.                 } 
23.             } 
24.         } 
25.     } 
26.  
27.     public List<TimeEntry> ReadTimerEntries(string filePath) 
28.     { 
29.         List<TimeEntry> results = new List<TimeEntry>(); 
30.         //open the file 
31.         var stream = File.OpenRead(filePath); 
32.         BinaryReader br = new BinaryReader(stream); 
33.         br.ReadBytes(4); //start file 
34.         while (!br.ReadBytes(4).SequenceEqual(new byte[] { 0x13, 0, 0, 0xC0 })) 
35.         { 
36.             //first 4 bytes are already consumed 
37.             ReadRound(br, results); 
38.         } 
39.         return results; 
40.     } 
41.  
42.  
43.     private void ReadRound(BinaryReader br, List<TimeEntry> itemList) 
44.     {             
45.         br.ReadBytes((16 * 5));// guff 
46.          
47.         br.ReadBytes(8);// start date 
48.         byte seconds = br.ReadByte(); 
49.         byte minutes = br.ReadByte(); 
50.         byte hour = br.ReadByte(); 
51.         byte day = br.ReadByte(); 
52.         byte month = br.ReadByte(); 
53.         int year = br.ReadInt16(); 
54.         br.ReadByte(); //unknown use 
55.         TimeEntry entry = new TimeEntry(); 
56.         entry.EntryDate = new DateTime(year, month, day, hour, minutes, seconds); 
57.         itemList.Add(entry); 
58.         br.ReadBytes(4); //end date 
59.  
60.         br.ReadBytes(4); //start laps 
61.         while (!br.ReadBytes(4).SequenceEqual(new byte[] { 0x10, 0, 0, 0xC0  })) 
62.         { 
63.             br.ReadBytes(4); //start lap 
64.             int lapMilliseconds = br.ReadInt32(); //4 bytes 
65.             TimeSpan lapTime = TimeSpan.FromMilliseconds(lapMilliseconds); 
66.             entry.Laps.Add(new TimeSpan(0, 0, 0, 0, lapMilliseconds)); 
67.             br.ReadBytes(4); //end lap 
68.         } 
69.         br.ReadBytes(4); //end round 
70.     } 
71.  
72. } 
73.  
74. public class TimeEntry 
75. { 
76.     public DateTime EntryDate; 
77.     public List<TimeSpan> Laps = new List<TimeSpan>(); 
78. } 

Terminator Salvation – coming to cinemas May 21

Ahh, the Terminator movie that we’ve always wanted – entirely set in the future, focusing on the war between man and machine.  I’ve always wondered when they would show the future they hinted at, a void slightly filled by the Terminator TV series (Sarah Connor Chronicles).  Another point of note is that this coming instalment is but the beginning of a Terminator revival – part of a new trilogy apparently.  And you can’t go wrong with Christian Bale as John Connor.

Reading database tables in Powershell

Firstly, a correction.  Last time I posted about Powershell, I came up with something that did the job, but was, well, a tat long winded.  Kind of like building a skyscraper so that you can store garden tools in the basement.  Basically I didn’t realise that the Sort cmdlet already had a –Unique switch.  Thanks to Stephen Mills for pointing it out:

import-csv c:\mydata.csv | select Category, Subcategory | sort category, subcategory -Unique | Group-Object -Property category

Now to the business at hand – reading database tables.  Consider a situation where you’re asked to read in the data in a database table into some readable form, for reference, printing or whatnot.  Rather than using SQL Management Studio to query the results, selecting the entire results grid, copying it, pasting into Excel (if available on the same machine), or into a text file, then getting it into Excel – you could just use Powershell to connect to the DB and dump out the results into a HTML table.  Behold:

1. # Parse Database tables 
2. # This will connect to a database, do a "select *" on a table or view, and produce a html file with the data in a table. 
3. # Note: don't forget to loosen up your execution policy "Set-ExecutionPolicy Unrestricted" 
4. # and the connection string is ADO style : "Data Source=database;Initial Catalog=oesc_offerman;User Id =username;Password=password;Trusted_Connection=False;" 
5.  
6. param ( 
7. $connectionString = $(throw "Specify connection string" ), 
8. $tableName = $(throw "Specify a table or view name"), 
9. $outputPath =  $(throw "Specify output path") 
10. ) 
11.  
12. echo ("Processing " + $tableName) 
13. $table = new-object System.Data.DataTable; 
14. $sqlConn = new-object System.Data.SqlClient.SqlConnection($connectionString); 
15. $sqlConn.Open(); 
16. $adapter = new-object System.Data.SqlClient.SqlDataAdapter(("select * from " +$tableName),$sqlConn); 
17. $adapter.Fill($table); 
18. $sqlConn.Close(); 
19. $table.Rows | ConvertTo-Html | out-file $outputPath 

Fish blimp

Had to share this one – from a homemade airship competition, footage of a small blip that uses a tail fin for propulsion – it looks very graceful, especially for a blimp – sure it’s not encumbered, with no wind, but it’s still mesmerising to watch:

Air Art from flip on Vimeo.

“SELECT DISTINCT” in Excel and Powershell

Just recently at work we had to import a large dataset into a database, but two of the columns were “category” and “subcategory”.  Naturally we wanted these in a separate table, so we needed a way of parsing through the existing data and get the unique categories, and the unique matching subcategories for each.

Excel:

Firstly, there’s the Excel ‘07 way (most likely also possible in other versions too):

Select the two columns that hold your categories and subcategories. Go to the “Advanced” menu item in Filtering:

The presented dialog has the option of “Unique Records Only”

Done, it’s the equivalent of doing a “distinct” in SQL.  Now sort the data, and you’re ready to go.

Powershell:

But no discussion of parsing/manipulating data is complete without mentioning Powershell – surely we can do this in PS.  Let’s assume that the data was saved in csv form (you could always save your Excel spreadsheet as .csv).

Luckily, there’s already a command that is able to parse csv’s for us: “Import-Csv”.  This cmdlet will parse the csv and create objects that have properties named after the columns – in the below image you can see that the object has a “Category” and “SubCategory” property.

Now that we have this collection of objects, use your favourite way of flushing out duplicates.  I’m keen on building up a new list, checking on each insert that the same entry doesn’t exist:

1. $temp = @{}  
2. $data | foreach {  
3.       if ($temp.ContainsKey($_.Category))  
4.       {  
5.         #check if subcategory exists, insert subcategory if doesnt  
6.         if ($temp[$_.Category].ContainsValue($_.Subcategory) -eq $false)  
7.         {  
8.             $temp[$_.Category].Add($_.Subcategory, $_.Subcategory);  
9.         }  
10.     }  
11.     else  
12.     {  
13.         #put it in:  
14.         $temp.Add($_.Category, @{$_.Subcategory = $_.Subcategory});  
15.     }  
16. } 

This is all well and good, but there are issues.  The "Category” property names are hardcoded, and it depends on an existing $data variable.  Ideally we want a cmdlet that can support piping, and will let us specify the names of the columns.

Luckily for us, Powershell is able support the following constructs:

What happened there is by putting the variable in brackets, PS will evaluate the variable and put it in the script.  I was able to create a variable $somestring, which had a string value of “Subcategory”.  When I used it in brackets, it expanded out to “Subcategory” in the script, and functioned in the same way as $data[2].Subcategory.  Neat.  This is an invaluable feature in scripting.

So we replacing all places where we previously had “Category” and “Subcategory” with expansions, and add them as required parameters.

Last thing, we need to get rid of the assumption of pre-existing $data variable.  Ideally the input will be piped in.  No problem, the reserved $input variable is the piped in parameters.

So now we can call it like so:

import-csv c:\MyTest.csv | c:\categoryParser.ps1 "Category" "Subcategory"

The final script looks like this:

1. param ( 
2. $categoryName = $(throw "Specify category column name" ), 
3. $subcategoryName = $(throw "specify subcategory name") 
4. ) 
5.  
6. $temp = @{} 
7. $input | foreach { 
8.     if ($temp.ContainsKey($_.($categoryName))) 
9.     { 
10.          
11.         #check if subcategory exists, insert subcategory if doesnt 
12.         if ($temp[$_.($categoryName)].ContainsValue($_.($subcategoryName)) -eq $false) 
13.         { 
14.             $temp[$_.($categoryName)].Add($_.($subcategoryName), $_.($subcategoryName)); 
15.         } 
16.     } 
17.     else 
18.     { 
19.         #put it in: 
20.         $temp.Add($_.($categoryName), @{$_.($subcategoryName) = $_.($subcategoryName)}); 
21.     } 
22. } 
23.  
24. #dump out the output 
25. $temp 

And produces something like this, a hashtable of objects each of which has the category name, and a hashtable of subcategories. 

From here it would be easy to traverse them all with two nested foreach loops, and do whatever with them, like generating INSERT statements.

Minotaur in a China Shop

I have a soft spot for independent games – possibly the last bastion of innovation in computer gaming.  Came across a very amusing little independent game, about a Minotaur running a fine china shop.  The game is 3D, isometric perspective.  Runs in the browser.  The minotaur isn’t exactly light on his feet, and handles like fridge - thus there are two ways to win – trash your own shop to get an insurance payoff, or service customers.  Give it a go – most amusing.

http://blurst.com/minotaur-china-shop/play

Minotaur China Shop Trailer from Flashbang Studios on Vimeo.