Convert JSON ROWS In CSV as columns into another CSV file

PowerShell Help
1

My sample data looks i

{"Id":"value","RecordType":value,"CreationTime":"value","Operation":"value"}

{"Id":"value","RecordType":value,"CreationTime":"value","Operation":"value"}
{"Id":"value","RecordType":value,"CreationTime":"value","Operation":"value"}
I need the same data in another CSV file as below
id      RecordType  CreationTime    Operation
value   value            value           value
value   value            value           value

I tried Convert-FROM-JSON function by passing the CSV as below but its failing with error


$properties = @('Id', 'RecordType', 'CreationTime', 'Operation')
(Get-Content -Path-to_CSVfile -Raw | ConvertFrom-Json) |
    Select-Object -Property $properties |
    Export-Csv -NoTypeInformation -Path $path-to-new-csv-file

ConvertFrom-Json : Invalid JSON primitive: "id"

Please help if anyone has suggestion regarding the same

Thanks,
Anil
2

Parse each row with the appropriate column. Note that the JSON example you posted, that the RecordType is invalid. Assuming that real data is numeric, the ConvertFrom-JSON would work, but if it is a string value, then it is malformed JSON which is causing the error you mentioned. Guessing that RecordType is not a string, you could do something like so:

$csv = @()
$csv += [pscustomobject]@{
    Column1 = 'Foo'
    Column2 = 'Faa'
    Column3 = '{"Id":"value","RecordType":1,"CreationTime":"value","Operation":"value"}'
}
$csv += [pscustomobject]@{
    Column1 = 'Foo'
    Column2 = 'Faa'
    Column3 = '{"Id":"value2","RecordType":2,"CreationTime":"value2","Operation":"value2"}'
}

$results = foreach ($row in $csv) {
    $row.Column3 | ConvertFrom-Json
}

$results

Output:

PS C:\Users\rasim> $csv

Column1 Column2 Column3                                                                    
------- ------- -------                                                                    
Foo     Faa     {"Id":"value","RecordType":1,"CreationTime":"value","Operation":"value"}   
Foo     Faa     {"Id":"value2","RecordType":2,"CreationTime":"value2","Operation":"value2"}



PS C:\Users\rasim> $results

Id     RecordType CreationTime Operation
--     ---------- ------------ ---------
value           1 value        value    
value2          2 value2       value2