ImagineThat ImagineThat - 16 days ago 7
JSON Question

Out-File a csv with specific format/information

I'm having trouble getting my head around how to take the two "properties" of each object within a json and output them into a csv. The two properties are "name" and "jobtitle" and I figure there should be a way to extract them.

$employees= ConvertFrom-Json $json
foreach ($person in $employees.information) {
# $person.name $person.jobtitle
}

Answer

No need for a loop, that should be bread-and-butter PowerShell territory. Feed the collection to a pipeline, use Select-Object to pick out the properties you care about, and then feed the pipeline to Export-CSV which handles the CSV header row, putting them on the same line, putting commas between them, quoting, etc.

$employees = ConvertFrom-Json $json

$employees.information | Select-Object name, jobtitle | Export-CSV -Path out.csv -NoTypeInformation

The only weird bit is -NoTypeInformation which stops it from putting PowerShell-specific markers for Integer/String/etc. types in the CSV, things which Excel (for example) won't read.

e.g. three objects with X,Y,Z properties - select just X and Z, and make them a CSV:

PS C:\> $Things = @(
     [PSCustomObject]@{X=1; Y=2; Z=3}
    ,[PSCustomObject]@{X=4; Y=5; Z=6}
    ,[PSCustomObject]@{X=7; Y=8; Z=9}
    )

$Things | Select-Object X,Z | ConvertTo-Csv -NoTypeInformation


"X","Z"
"1","3"
"4","6"
"7","9"