CODEWITHSUNDEEP
regexpowershellpowershell-2.0
eltaco431
eltaco431

Reputation: 850

PowerShell: Pull table from a string

I have a command that I ran on PowerShell 2.0 that gives me the output below.

PowerShell Output

Everything in the screenshot above is one giant string. I want to pull out the table part of the string so that I can potentially format it as a list. Ultimately, I want to output to look like:

INSTANCE_NAME: Sample Name
STATUS: MOUNTED
DATABASE_STATUS: ACTIVE

My first thought was the use regex to pull out the table. I thought something like this might work, but I've so far been unsuccessful.

$tabletext = [regex]::match($rawtext, "(INSTANCE_NAME(.+\r\n)+)")

EDIT: Here is the text as a string.

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 20 16:34:57 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
sample_name     OPEN         ACTIVE

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Upvotes: 1

Views: 351

Answers (1)

qbik
qbik

Reputation: 5938

I've done something very similar to parse Firebird sql output.

Here's a script that works on your sample data:

function parse-headers($lines) {
   $h = $lines[0]

    $headers = $h.Split(" ", [System.StringSplitOptions]::RemoveEmptyEntries)
    $headers = $headers | %  { 
        new-object pscustomobject -property @{ 
            Length = $_.Length
            Offset = $h.IndexOf($_) 
            Text = $_ 
            }
    }     
    for($i = 1; $i -lt $headers.length; $i++) {
        $headers[$i-1].Length = $headers[$i].Offset - $headers[$i-1].Offset - 1
    }
    $headers[$header.length-1].Length = $h.length - $headers[$header.length-1].Offset

    return $headers
}

function parse-sqloutput($lines) {
    $headers = parse-headers $lines
    $result = @()

    for($l = 2; $l -lt $lines.Length; $l++) {
        $line = $lines[$l]
        $headernames = $headers | % { $h = @{} } { $h[$_.Text] = $null } { $h }
        $r = New-Object -type pscustomobject -Property $headernames
        for($i = 0; $i -lt $headers.length; $i++) {
            try {
                $h = $headers[$i]
                $name = $h.text
                if ($i -eq $headers.length - 1) {
                    $value = $line.Substring($h.Offset).Trim()
                }
                else {
                    $value = $line.Substring($h.Offset, $h.Length).Trim()
                }
                $r.$name = $value
            } catch {
                Write-Warning "failed to parse line $l col $i"
                throw    
            }
        }
        $result += $r
    }
    return $result
}

function get-sqltable($sqlout) {

    #find sql table output
    $startidx = -1
    $endidx = -1

    for($i = 0; $i -lt $sqlout.Length; $i++) {
        $line = $sqlout[$i]
        if ($line -match "^\s*([\-]+\s*)+$") { 
            $startidx = $i - 1 
        }
        if ($startidx -ge 0 -and $line -match "^\s*$") { 
            $endidx = $i 
        }
        if ($startidx -ge 0 -and $endidx -ge 0) { break }
    }

    $sqlout = $sqlout | select -Skip $startidx -First ($endidx-$startidx)
    return $sqlout
}

$sqlout = get-content "sqlout.txt" | out-string 

#split giant string into lines
$sqlout = $sqlout | Split-String "`r`n"
$sqlout = get-sqltable $sqlout

#we should now have only sql table in $sqlout
$result = parse-sqloutput $sqlout

$result | Format-List

The idea is:

  1. Find a line that contains only strings of - chars and assume it marks header row.
  2. Look for first empty line after header row and assume it is the end of the table output.
  3. Parse the header row, get columns' names and lengths.
  4. Get values basing on parsed column lengths.

Upvotes: 2

Related Questions