Home > other >  Convert text file containing PowerShell Object Notations to CSV
Convert text file containing PowerShell Object Notations to CSV

Time:07-07

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.

CodePudding user response:

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"

CodePudding user response:

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() }
  • Related