Convert text file containing PowerShell Object Notations to CSV

Question

I have a text file (PsonObjects.txt) that contains PowerShell Object Notation as follows:

@{Computer=Dektop123; ServiceA=Running; ServiceB=Running; RunspaceId=1a-1b}
@{Computer=Dektop456; ServiceA=Stopped; ServiceB=Stopped; RunspaceId=2b-2c}
@{Computer=Laptop123; ServiceA=NotFound; ServiceB=Running; RunspaceId=3c-4c}
@{ServiceA=NotFound; Computer=Laptop456; ServiceB=Running; RunspaceId=4d-5d}

I would like to convert this Pson file into a Csv file, but have been unable to throughout my attempts.

The problem is sometimes an object's properties are in a different order than the previous object. Like the last line in the above example. Therefor simply converting the semicolon (';') to a comma (',') and removing the '@{}' characters does not produce a properly ordered Csv file.

Is there an easy way to convert this Pson (Powershell object notation) file into a CSV file?

Ive tried a few different methods, but I keep getting either the length of the string, or hashtable output (IsReadOnly, IsFixedSize, IsSychronized, keys, Values, SyncRoot, Count) in my CSV output file.

Non Working code:

$inputFile = PsonObjects.txt
$CsvFileName = CsvOutput.csv
foreach($line in (Get-Contnet $inputfile)){
  $newline = $line | convertFrom-StringData
  $newline | Export-CSV $CsvFileName -Append 
}

Any assistance or suggestions with this problem are appreciated.

As an aside: Your text file uses for-display representations of custom objects, which aren't generally suited for programmatic processing. If possible, use a structured text format such as CSV to begin with. — mklement0
– mklement0, Commented Jul 6, 2022 at 13:42

zett42 · Accepted Answer · 2022-07-06 15:44:05Z

ConvertFrom-StringData outputs an unsorted Hashtable, where the elements can be in any order, so it is of no use here.

Here is a possible solution, using the -split operator and a sorting step, using an ordered Hashtable:

$inputFile = 'PsonObjects.txt'
$csvFileName = 'CsvOutput.csv'

# The order of columns in the output CSV
$keyOrder = 'Computer','ServiceA','ServiceB','RunspaceId'

Get-Content $inputfile | ForEach-Object {
    $line = $_.Trim('@{}')   # Remove unwanted characters from current line

    # First parse the current line into an unordered hashtable
    $ht = @{}
    foreach( $field in $line -split '; ' ) {
        $key, $value = $field -split '='
        $ht[ $key ] = $value
    }   

    # Using the order specified by $keyOrder, create an ordered hashtable
    $orderedHt = [ordered] @{}
    foreach( $key in $keyOrder ) {
        $orderedHt[ $key ] = $ht[ $key ]
    }

    [PSCustomObject] $orderedHt     # Convert hashtable to custom object

} | Export-Csv $csvFileName

Output:

"Computer","ServiceA","ServiceB","RunspaceId"
"Dektop123","Running","Running","1a-1b"
"Dektop456","Stopped","Stopped","2b-2c" 
"Laptop123","NotFound","Running","3c-4c"
"Laptop456","NotFound","Running","4d-5d"

Santiago Squarzon · Accepted Answer · 2022-07-06 16:48:24Z

Here is another alternative to zett42's helpful answer using the Parser Class:

$parse = Get-Content PsonObjects.txt -Raw
$ast   = [System.Management.Automation.Language.Parser]::ParseInput($parse, [ref] $null, [ref] $null)
$ast.EndBlock.Statements.PipelineElements.Expression | ForEach-Object {
    $out = [ordered]@{}
    foreach($pair in $_.KeyValuePairs) {
        $out[$pair.Item1.Extent.Text] = $pair.Item2.Extent.Text
    }
    [pscustomobject] $out
}

Code would be much simpler if the Values where quoted, since we would have the option to use .SafeGetValue(), as you have them right now the parser is interpreting them as commands instead of strings.

$test = @'
@{Computer='Dektop123'; ServiceA='Running'; ServiceB='Running'; RunspaceId='1a-1b'}
@{Computer='Dektop456'; ServiceA='Stopped'; ServiceB='Stopped'; RunspaceId='2b-2c'}
'@

[System.Management.Automation.Language.Parser]::ParseInput($test, [ref] $null, [ref] $null).
    EndBlock.Statements.PipelineElements.Expression.ForEach{ [pscustomobject] $_.SafeGetValue() }

Collectives™ on Stack Overflow

Convert text file containing PowerShell Object Notations to CSV

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related