locked
Unable to convert json to CSV format RRS feed

  • Question

  • Hi All

    I am new to Powershell script and working on conversion of below Json format to csv.

    I am trying to convert below JSON format into CSV ,but we are unable to convert into CSV by power shell.

    I would like to capture Details and finalclass into CSV , 

    {
      "code": 0,
      "message": "Found: 8505",
      "objects": {
        "ABC::11111": {
          "class": "ABC",
          "code": 0,
          "fields": {
            "finalclass": "ABC",
            "friendlyname": "ABC",
    "details":"I am sample",
                  },
          "key": "11111",
          "message": ""
        },
        "ABC::11112": {
          "class": "ABC",
          "code": 0,
          "fields": {
            "finalclass": "ABC",
            "friendlyname": "ABC",
    "details":"I am sample2",
            
          },
          "key": "11112",
          "message": ""
        }
    }
    }

    Monday, October 15, 2018 3:13 AM

All replies

  • Your Json is not correctly formed and cannot be converted.


    \_(ツ)_/

    Monday, October 15, 2018 3:37 AM
  • I fixed the Json so that it would convert.  You have to understand that a hierarchical object cannot be converted into a CSV.

    $json = @'
    {
        "code": 0,
        "message": "Found: 8505",
        "objects": {
            "ABC::11111": {
                "class": "ABC",
                "code": 0,
                "fields": {
                     "finalclass": "ABC",
                     "friendlyname": "ABC",
                     "details":"I am sample"
                },
                "key": "11111",
                "message": ""
            },
            "ABC::11112": {
                "class": "ABC",
                "code": 0,
                "fields": {
                    "finalclass": "ABC",
                    "friendlyname": "ABC",
                    "details":"I am sample2"  
                },
               "key": "11112",
               "message": ""
            }
        }
    }
    '@
    
    
    
    $json | ConvertFrom-Json | ConvertTo-Csv
    
    Even with the fixes the Json is corrupt and cannot be extracted without extensive coding.


    \_(ツ)_/

    Monday, October 15, 2018 3:52 AM
  • Here are some hints:

    PS D:\scripts> $obj = $json | ConvertFrom-Json
    PS D:\scripts> $obj.objects.'ABC::11111' class : ABC code : 0 fields : @{finalclass=ABC; friendlyname=ABC; details=I am sample} key : 11111 message : PS D:\scripts> $obj.objects.'ABC::11111'.fields finalclass friendlyname details ---------- ------------ ------- ABC ABC I am sample PS D:\scripts> $obj.objects.'ABC::11112'.fields finalclass friendlyname details ---------- ------------ ------- ABC ABC I am sample2 PS D:\scripts>


    \_(ツ)_/



    • Edited by jrv Monday, October 15, 2018 3:57 AM
    Monday, October 15, 2018 3:54 AM
  • Thanks a lot Moderator !!

    This is actually a webservices response and I can't make any much changes in that Response.

    Is it possible to get some sample code which can help to get this csv format since i tried lot of combination as below :

       

    Get-Content D:\mmudgal071017\PowerScript\2.json -Raw |
        ConvertFrom-Json | 
        Select -Expand fields |

         Export-Csv D:\mmudgal071017\PowerScript\2.csv -NoTypeInformation

     
    Select : Property "fields" cannot be found.
    At line:3 char:5
    +     Select -Expand fields |
    +     ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (@{code=0; message=Found: 8505; objects=}:PSObject) [Select-Object], PSArgumentExcept 
       ion
        + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand


    Monday, October 15, 2018 4:05 AM
  • No.  If it is bad json then you are out of luck.


    \_(ツ)_/

    Monday, October 15, 2018 4:13 AM
  • $a = get-content file.json | convertfrom-json
    $a.objects.'abc::11111'.fields

    finalclass friendlyname details
    ---------- ------------ -------
    ABC ABC I am sample

    $
    a.objects.'abc::11111'.fields | export-csv file.csv
    • Edited by JS2010 Tuesday, October 16, 2018 3:47 AM
    Monday, October 15, 2018 1:27 PM