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