Using PowerShell to represent Base 26 as the uppercase English Alphabet

Today I was asked to do something that seemed simple, until I actually had to do it. A coworker had a database with two fields he wanted renamed in a specific way. For our example, lets call them ProductNumber and ProductName. He wanted ProductNumber to be sequential (1, 2, 3 . . .) and the ProductName fields to be called “Product A”, “Product B” . . . “Product Z”, “Product AA” etc. So this suddenly became a non-trivial problem if you had more than 26 rows, which of course I did.

So I rolled up my sleeve, got a fresh cup of coffee, and got to work. Populating ProductNumber was easy enough using a Common Table Expression (CTE) with a ROW_NUMBER(). Then I realized I could think of the English alphabet as symbols for a base 26 number system, with AA following Z and so on. The only problem was I couldn’t express that in a set based way for a clean T-SQL implementation. No problem, I’d just generate the T-SQL to make a giant mapping table in PowerShell!

I am ashamed to admit I had to look up the algorithim for converting from base 10 to another number. I was also surprised to discover that the first result google returned me was this tripod page.

The algorithm is as follows.

  1. Start with an empty string which becomes the return value
  2. While the value is greater than the base get the remainder of the value divided by the base. Convert that to its letter and prepend that to the return value
  3. Repeat step 2 with the quotient of the value over the base.
  4. When the quotient is less than the base, prepend that to the string instead.

It seemed simple enough, but there were some headaches.

The first thing I discovered was that when you divide integers in PowerShell, you get a float as a result. Also casting it back to an int rounds instead of truncating the results. I was expecting the opposite in both cases, because that is how C# behaves. I ended up using the unwieldy combination of Math.Floor() and a cast in the form [int][math]::Floor($currVal / 26) to resolve this. The MSDN technet has an article that recommends the more unwieldy [Math]::floor([int] $currVal / [int] 26), but I proved that my terser method gives the same results.

Then I had problems with how to display powers of 26. The way it was supposed to work was that 1 = A, 24 = X, 25 = Y, 26 = Z and 27 = AA. However, depending on how I did it I ended up with 26 = AZ or 27 = BA. I could not account for this edge case, nor compensate for it with special conditions.

Then it dawned on me, A needed to be equal to zero not one. A base 10 system deals with the digits 0-9. Base 2 deals with 0 and 1. Base 16 deals with 0-F and F is 15. Once I rewrote my script to work that way, edge cases disappeared, and things just worked.

The script

function Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value)  {
	$currVal = $value;
	$returnVal = '';
	while ($currVal -ge 26) {
		$returnVal = [char](($currVal) % 26 + 65) + $returnVal;
		$currVal =  [int][math]::Floor($currVal / 26)
	}
	$returnVal = [char](($currVal) + 64) + $returnVal;
	
	return $returnVal
}

If its not clear how I generated upper case letters, the ASCII codes for A through Z are 65 through 90, and casting an integer to a char converts it to its ASCII code. Ergo, the expression [char]65 evaluates to “A”.

So now here’s the function in action:

1 .. 100 | ForEach-Object {
	$_ | Convert-ToLetters
}

Happy Scripting!

Making an RDP connection to a server you just rebooted with PowerShell

Update: With the release of PowerShell 3.0 imminent, I wish to draw everyone’s attention to Shay Levy’s (blog|twitter) simplified Posh v3 version.

It’s an all to familiar story. You  need to reboot a server, and then you need to start a remote desktop connection into it. So what do you do? You open up a command prompt, type ping -t <HOSTNAME> and wait until the server responds to pings. When that happens, you keep trying to connect via remote desktop, until it works. There’s got to be an easier way. You should write a PowerShell script to automate the process. However, its one of those things that not quite annoying enough to get you to actually take action and write the script. Luckily, thanks to the power of twitter, I reached a tipping point this week, and wrote the script. It all started out with some innocent whining:

You know what I need in an RDP client. I need an “”I just rebooted so ping it for me and autoreconnect”. 2 days ago via web · powered by @socialditto

Then d0tk0m and Yanni Robel retweeted my whining. They say necessity is the mother of invention. In this case the commiseration off two tweeps was the father. So I spent a Saturday with PowerGUI, and came up with a script to solve the problem.

Planing stage

I wanted my script to automate what I already did. From the perspective a system administrator that wants to reboot a server and then remote desktop into it, the following happens.

  1. All the processes, including the remote desktop service (termsrv.dll) are shut down. When that happens the remote desktop port (default 3389) no longer has anything listening on it.
  2. Eventually the network adapter is shutdown and it will stop responding to ICMP echo requests, or pings.
  3. The server will finish shutting down, the BIOS will POST, and Windows will begin booting
  4. Eventually the network adapter will come up and start responding to pings.
  5. The remote desktop service will start and bind to the remote desktop port.

Therefore, I made my script to do the following.

  1. Ping the server until it answered five successive ping requests. Yes this might be naive and optimistic in many cases. However, it worked in my use case. I used the Send() method of the .NET System.Net.NetworkInformation.Ping class to do this.
  2. Once I was sure the host was up, I’d try to connect to it on port 3389, or another port if I passed a different port as a parameter. To do this I used the System.Net.Sockets.TcpClient class.
  3. After this it was simply a matter of passing the right parameters to the remote desktop client, mstsc.exe. I initially attempted to use the simple & mstsc <Arguments>. However, that didn’t work to well so I ended up resorting to Invoke-Expression.

The script

Below is the current version of the script, hosted on poshcode.org. The current version is stored in gist repository. While you are free to post changes to poshcode.org (or anywhere), in accordance with the license, I’d prefer if you notified me of any changes so that they may be placed in the gist git repo.