Convert JSON ROWS In CSV as columns into another CSV file

Welcome Forums General PowerShell Q&A Convert JSON ROWS In CSV as columns into another CSV file

Viewing 1 reply thread
  • Author
    Posts
    • #188086
      Participant
      Topics: 1
      Replies: 0
      Points: 12
      Rank: Member

      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
    • #188110
      Participant
      Topics: 10
      Replies: 1284
      Points: 1,084
      Helping Hand
      Rank: Community Hero

      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 
      
Viewing 1 reply thread
  • You must be logged in to reply to this topic.