WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, August 2, 2012

Converting signed overpunch values in Powershell

Today I was faced with parsing some large text files to grab out numerical data (mainframe type source files) that are formatted with the signed "overpunch" technique.  Since I was doing the parsing in Powershell, I made a shorter version of the function I found online (a switch statement for each value) using a little regex.

Here it is, hope it saves you some time if ever needed.

    OverPunch is a mainframe technique to save one character for the + or - value on a numeric field by swapping the right most digit 
    {,A,B,C,D,E,F,G,H,I -> represent positive numbers 0-9
    },J,K,L,M,N,O,P,Q,R -> represent negative numbers 0-9 
    0000052} = -5.20
Function Convert-OverPunch

    #Grab everything passed in but the overpunch char(last char in field) and add implied decimal
    $mainval = "{0}.{1}" -f $inputval.Trim().Substring(0,($inputVal.Length - 2)) , $inputval.Trim().Substring($inputval.Length-2,1)
    #Grab the overpunch the character
    $punchChar = $inputval.Substring(($inputVal.Length -1),1)
    Write-Verbose "Punch Char $punchChar from $mainval"

    $mainval +=  switch -regex ($punchChar) 
                    ['{}'] {0} 
                    [AJ] {1} 
                    [BK] {2} 
                    [CL] {3} 
                    [DM] {4} 
                    [EN] {5} 
                    [FO] {6} 
                    [GP] {7}
                    [HQ] {8} 
                    [IR] {9}
                    default {$null}

    [decimal]$NumberVal = [System.Decimal]::Parse($mainval)

    IF($punchChar -match "[}JKLMNOPQR]") {
        $NumberVal *= -1;

    return $NumberVal