Wednesday, 17 September 2014

Text templating with … (gasp) … Excel!

Excel has many unlikely uses (did you know that Excel 97 contained a hidden Flight Simulator?). I’ve just reminded myself of another one: fast text templating.

I needed to convert a data structure that looked like this


into one that looked like this


In other words, I needed to convert from a delimited text string into XML elements.

It took me about 60 seconds in Excel. Here’s how:

  1. I copied the text containing all the keywords and pasted it into a single cell of an Excel spreadsheet
  2. I selected the cell, and clicked on the Text to Columns tool (you find this in the Data tab)image
  3. In the Text to Columns Wizard, I indicated that the data was Delimited, and then entered the appropriate delimiter – the pipe (‘|’) character in this case. When I clicked Finish, Excel split each keyword into its own cell, going across the sheet.image
  4. Next, I needed the keywords arranged vertically instead of horizontally – in rows instead of columns. So I selected all the columns by clicking the first cell, then pressing Ctrl+Shift+[Right Arrow] to select all the way to the last keyword. Ctrl-C copied all the keywords. After clicking in an empty cell, I selected the Paste Special option. In the Paste Special dialog, right at the bottom, you’ll find the Transpose option, which will convert columns of data into rows, and vice versa.image
  5. In the cell next to the first keyword, I typed a formula that would wrap the keyword in the XML boilerplate. To concatenate strings use ‘&’, and to include quotes in a string use a double quote. So the formula I needed was
    ="<Keyword Name=""" & A3 & """ />"
  6. I selected that first cell again, and double-clicked the solid square at the bottom right of the cell’s selection border. This replicated the formula all the way down the page to the last row with something in it.
  7. Did the Ctrl-C/Ctrl-V dance to copy and paste the generated XML into my code file.
  8. Job’s a good ‘un!

Monday, 10 February 2014

Getting Started with SignalR - and not a Chat room in sight!

You might have heard of SignalR, a real-time communications library for the web from Microsoft. It’s open source, it’s hip (it supports WebSockets!) – and just about all the Getting Started tutorials are about Chat applications. How many times have you had to write a Chat application in your career? I’d bet a two-toed sloth could keep count for you on one foot and still have digits to spare.

I want to show you how SignalR can solve elegantly a problem you will almost certainly have faced at some point: reporting progress of long-running operations on web pages. I say you’ve probably faced the problem – and if you’re anything like me, you’ve probably ducked out of solving it too, leaving your web site visitors in the tender care of a wait cursor while your web application does its thing in its own sweet time. Getting a server to report back to a web page has always been a fiddle, and many of the techniques in common use feel like hacks. SignalR changes all of that.

Read my three-part guest series on the Safari Books Online blog: