Sonntag, 14. November 2010

I can code Bad Influenced style too

I hope there is a better way to do this, but if PowerShell enforces the use of work arounds, I'll use them.
Please, if you find something more elegant, please drop a note.

Well to make SQLPSX one of the most powerfull tools to extract data from SQL-Server or Oracle databases I just want to transform a datarow into a tabular representation.

$datarow | Format-Table -auto

seems a good start, but as described in

you need to pass the result to Out-String -width 10000 or as many characters you want.
Sorry can't say -1 for unlimited. (Perhaps in V3, if we cry load enough?)

Oops not all columns included, lets add -property *

$datarow | Format-Table -auto -property * | Out-String -width 10000

Oops what happens now?  At the end you get some unwanted addition columns:

RowError, RowState, Table, ItemArray,  HasErrors

A problem well described in With no work around supplied.

Go there and vote for it.

And here is the code, that gives me the wanted result. I agree, it is ugly, but its result is fine:

$columns = ''            
                            foreach ($i in 0.. ($res[0].Table.columns.count -1))            
                                if ($columns) { $columns +=  ', '+ $res[0].Table.Columns[$i].ColumnName }            
                                else { $columns = $res[0].Table.Columns[$i].ColumnName}            
                            $c = '($res | ft -Property ' + $columns + ' -auto | Out-string -width 10000 -stream ) -replace " *$", ""-replace "\.\.\.$", "" -join "`r`n" '            
                            $text = Invoke-expression $c            

I hope this helps



  1. $columns = $res[0].Table.columns | ? {"RowError","RowState","Table","ItemArray","HasErrors" -notcontains $_.ColumnName} | Select -exp ColumnName
    $res | Select $columns | ConvertTo-CSV

    ## :)

  2. Thanks, but I do not want to involve external files. I want to format the string and just insert it back into the editor.

  3. This does not involve external files.

  4. Gotcha, I though of Export-CSV. I'll check it again.

  5. Sorry doesn't work. Select-Object doesn't accept list of strings for property parameter:

    Select-Object : Cannot convert System.Management.Automation.PSObject to one of the following types {System.
    String, System.Management.Automation.ScriptBlock}.
    At line:1 char:15
    + $a[0] | select <<<< $columns
    + CategoryInfo : InvalidArgument: (:) [Select-Object], NotSupportedException
    + FullyQualifiedErrorId : DictionaryKeyUnknownType,Microsoft.PowerShell.Commands.SelectObjectCommand

    But it would be very nice, if it would.

  6. Best solution is based on a note by Shay Levy

    $text = ($_ | Select * -exclude RowError,RowState,Table,ItemArray,HasErrors | ft -Property * -auto | Out-string -width 10000 -stream ) -replace " *$", ""-replace "\.\.\.$", "" -join "`r`n"

  7. I took a more simplistic approach - it creates a little more work but is effective enough for what I'm doing (replace the [ or ] except when seen together like this - [] - with < or > below because this post mechanism sees them as what they are - tags).

    get-content c:\di1.html | foreach-object {$_ -replace "[th]RowError[/th][th]RowState[/th][th]Table[/th][th]ItemArray[/th][th]HasErrors[/th]", ""} | set-content c:\di2.html
    get-content c:\di2.html | foreach-object {$_ -replace "[td]Unchanged[/td][td]Table[/td][td]System.Object\[][/td][td]False[/td]",""} | set-content "c:\di3.html")