none
Powershell 5.0 JSON to CSV? RRS feed

  • General discussion

  • Hi

    following gives me a nice JSON which is transfromed to CSVs

    It works, just wondering if there is a smarter way to do this?

    I'm happy that PS 5.0 can handle bigger JSONs without having to go back to

    # $rawtext = Invoke-WebRequest -Uri $url
    # [void][System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")        
    # $jsonserial= New-Object -TypeName System.Web.Script.Serialization.JavaScriptSerializer
    # $jsonserial.MaxJsonLength = [int]::MaxValue
    # $json2 = $jsonserial.DeserializeObject($rawtext)

    $url = "https://data.england.nhs.uk/api/action/current_package_list_with_resources?limit=10" 
    $json = Invoke-RestMethod -Uri $url

    $extractiondate = "20150618"
    $path_results = ".\DataNhse_results_$extractiondate.csv"
    $path_resources = ".\DataNhse_resources_$extractiondate.csv"


    # Dataset details: Level 1
    $i=0
    $level1_results = foreach($row in $json.result )`
      {
        #Loop through input.
            $PHEC_result_ID = ($i+1) -join ","
            $result_name = ($row | %{"$($_.name)"}) -join ","

        #Build your flat custom object
        [pscustomobject]@{
                PHEC_result_ID = $PHEC_result_ID
                result_name = $result_name
        }
            $i++
      }

    $level1_results | Select-Object $Output | Export-Csv -NoTypeInformation -Path $path_results

    # Dataset details: Level 2:  resources
    $i=0
    $j=0
    $level2_results = foreach($row in $json.result )`
      {
        #Loop through input.
            $PHEC_result_ID = ($i+1) -join ","
               
             foreach($row2 in $json.result[$i].resources )`
               {
                 #Loop through
                    $PHEC_result_resources_ID = ($j+1) -join ","
                    $result_resources_description = ($row2 | %{"$($_.description)"}) -join ","
                    $result_resources_url = ($row2 | %{"$($_.url)"}) -join ","

            #Build your flat custom object
            [pscustomobject]@{
                        PHEC_result_ID = $PHEC_result_ID
                PHEC_result_resources_ID = $PHEC_result_resources_ID
                        result_resources_description = ($result_resources_description -replace "`r`n", "`\r`\n") -replace "`n", "`\n"
                        result_resources_url = $result_resources_url
              }
                   $j++
            }
            $i++
      }

    $level2_results | Select-Object $Output | Export-Csv -NoTypeInformation -Path $path_resources


    Thursday, June 18, 2015 6:18 PM