I had to break out some perl skills today. I was little rusty as I haven’t used it in volume for 2-3 years. However, it was the right tool for the job today. One of our rainmakers needed a favor for building a conversion table from an old Chart of Accounts to the newest version. All they had was a spreadsheet of about 4000 items where column “A” housed a formula of seemingly random numbers from column “B”. The assignments from old to new were a mixture of single elements and cell ranges that made it difficult, if not impossible to handle in Excel.

“Can you do anything with this?” Sure. No problem.

But, what was supposed to be an exercise in “Excel-foo”, ended up needing a bit of coding help.

#!/opt/local/bin/perl

while (<>) {
	chomp($_);
	@line1 = split(/,/);
	@line2 = split(/;/,$line1[1]);
	foreach (@line2) {
	    if($_ =~ /\:/) 
		{
		   @line3 = split(/:/);
		   print "K".$line1[0].",K".$line3[0].",range_begin\n";
		      for($i = $line3[0]+1; $i &amp;lt; $line3[1]; $i++) {
			  print "K".$line1[0].",K".$i.",range\n";
		      }
		      print "K".$line1[0].",K".$line3[1].",range_end\n";
		}
		else
		{
		   print "K".$line1[0].",K".$_.",single\n";
		}
	}	
}

This simply pulled the single cell references and the cell range references from a CSV file and populated all of the values between the start and stop values of each range. So the input was essentially a multi-value record a la a schemaless database and the output was more relational.

The result is another happy client. Enhancement to my reputation as a problem solver (go me!), and a chance to write some code. Always a good day.