Powershell - Json to csv - values in separate rows-CodePudding

I am trying to get the data into a csv from an InvokeWeb request. Currently there are only 2 different values with the individual values in it, but later also times more. It makes the headers of the table, and the correct number of columns, but throws the 2 different values into a cell in both rows. So how can I prevent it from looking like this? I have already read about AddMember or =, but did not find the right way.

$valstring = iwr -Proxy http://my-proxy -ProxyUseDefaultCredentials -Method GET -Uri https://my-URL -Headers @{'ContentTyp' = 'application/json';"X-Api-Key" = "my-API-Key"} -UseBasicParsing | Select-Object Content | foreach {$_.Content}
$pathToOutputFile = "H:\Wichtiges\json2csvexport.csv"

$deckung = $valstring | ConvertFrom-Json | ForEach-Object {
    foreach ($uuid in $_ ) {
            [PsCustomObject] @{
                name       = $_.name | Out-String
                version       = $_.version | Out-String
                active       = $_.active | Out-String         
        }
    } 
}

$deckung | Export-Csv $pathToOutputFile -NoTypeInformation -UseCulture

The Json-Data looks like that

[
    {
        "name":  "App1",
        "version":  "1.1.1",
        "uuid":  "a74a1969-f57d-437b-943f-d4c3dd3bc4bb",
        "active":  true,
        "metrics":  {
                        "high":  617,
                        "medium":  1250,
                        "low":  103,
                    }
    },
    {
        "name":  "App2",
        "version":  "1.3.1",
        "uuid":  "59eda14a-56a6-4dc6-8238-dd168bd0df3f",
        "active":  true,
        "metrics":  {
                        "high":  6,
                        "medium":  13,
                        "low":  0,
                    }
    }
]

But it always comes out like that (second and third together and fourth and fifth row together):

|name  |  version  |  active |
|------|-----------|---------|
|App1  |    1.1.1  |   true  |
|App2  |    1.3.1  |   true  |
|App1  |    1.1.1  |   true  |
|App2  |    1.3.1  |   true  |
-----------------------------

How can I change it to get one per row that it looks like:

|name  |  version  |  active |
|------|-----------|---------|
|App1  |    1.1.1  |   true  |
|App2  |    1.3.1  |   true  |
-----------------------------

Thx Kind regards

Edit (Solution): That solution works for me, because I can put things from mectrics into the csv and give the Heading an other name:

Select-Object @{Name='Name';Expression={$_.name}}, 
  @{Name='Version';Expression={$_.version}}, 
  @{Name='Active';Expression={$_.Active}},
  @{Name='Risk-High';Expression={$_.metrics.high}}

Edit (Another question): How can I prevent numbers in the csv from being converted to dates? Thx

CodePudding user response：

Try the following:

$pathToOutputFile = "H:\Wichtiges\json2csvexport.csv"

# Note:
#  * the use of Invoke-*RestMethod*, which has ConvertFrom-Json *built in*.
#  * the (...) around it, to ensure that the array is *enumerated*.
(Invoke-RestMehod -Proxy http://my-proxy -ProxyUseDefaultCredentials -Method GET -Uri https://my-URL -Headers @{'ContentType' = 'application/json';"X-Api-Key" = "my-API-Key"}) |
  Select-Object name, version, active |
  Export-Csv $pathToOutputFile -NoTypeInformation -UseCulture

As for what you tried:

Your foreach ($uuid in $_ ) loop mistakenly used $_ - the array to loop over - instead of $uuid - the iteration variable containing the element at hand.
- The simpler alternative, to explicit enumeration is to wrap a call to ConvertFrom-Json / Invoke-RestMethod in (...) in order to automatically force enumeration of an array being returned.
- Note that this is no longer necessary in PowerShell (Core) 7 , where arrays are enumerated by default (and you need -NoEnumerate in order to send an array as a whole through the pipeline, which is the invariable default behavior in Windows PowerShell).
For simple stringification of a value, Out-String is the wrong tool, primarily because it - unexpectedly - adds a trailing newline to the output - see GitHub issue #14444.
- Use a [string] cast instead; however, given that Export-Csv implicitly performs such a stringification for you, there's no need to do it explicitly, allowing you to use a simple Select-Object to pick the properties of interest, as shown above.