PowerShell: Convert RPT to JSON

05 Jul, 2022

I have only recently started working with SQL Server Management Studio. When you make a SQL query, you have the option of outputting the results in several ways: printing it to the screen as text, printing it to the screen as a table/grid, or outputting it to a local file. I like outputting to a local file, because I can then manipulate it with PowerShell or Power Query or Power Something Or Other. The default file format for outputs from SQL Server Management Studio is .rpt.

.rpt is a text format for tables that represents the table as it would look on a screen, or printed. This means that all the columns are defined by spaces, which is a bit hard to work with when you want a computer to consume it.

Now, I know that there are some easy ways to get around this (see this article for example), including forcing Excel to open it, or changing a setting in SSMS itself to output files as .csv.

But where is the fun in that? As the old saying goes, "the obstacle is the way". It is perfectly obvious that what needs to be done here is to write a PowerShell function that takes .rpot files and converts it to .json (it can also convert to .csv with slight modification, I'll leave that part up to you. )

Here it is:

function Convert-RptToJson ([string] $filename) {  
    $raw = (Get-Content $filename)

    $headings = ($raw[0] -split "\s+")
    $columnLines = $raw[1]
    $lengths = @(0) + $(($columnLines -split "\s") | % { $_.Length + 1})
    [array] $positions = @(0)

    for ($i = 0; $i -lt $lengths.Length; $i++) {
        if (($i - 1) -le 0) {
            $j = 0
        } else {
            $j = $i - 1
        }
        $positions += $positions[$i] + $lengths[$i]
    }

    $positions = $positions | Select-Object -skip 1

    function parseLineValues ([string] $thisLine) {
        return $values = for ($i = 0; $i -lt $positions.Length; $i++) { 
            $thisLine.Substring($positions[$i], $lengths[$i+1]).Trim() 
        }
    }

    $data = $raw | 
        Select-Object -skip 2 | 
        % {
            $thisLine = $_
            $values = parseLineValues $thisLine
            $object = [PSCustomObject] @{}
            for ($i = 0; $i -lt $headings.Length; $i++) {
                $object | Add-Member -NotePropertyName $headings[$i] -NotePropertyValue $values[$i] -Force
            }
            return $object
        }

    $outFilename = ($filename.Split(".")[0])
    
    $data | 
        ConvertTo-Json | 
        Set-Content "$outFilename.json"

}

#powershell #sql #ssms