CODEWITHSUNDEEP
jsonpowershellcsvformat
Orion
Orion

Reputation: 1

Extracting fields to CSV from a JSON file with Powershell

i have a json extracted from a API exact like this:

{
    "LPEONASVVAP0": {
        "LPEONASVVAP0": {
            "id": "urn:vcloud:vm:f526d27d-e0f9-4d4f-ae81-4824e397c027",
            "name": "LPEONASVVAP0",
            "description": "_vm_desc_",
            "dateCreated": "2021-04-06T14:56:09.640+0000"
        }
    },
    "WDEONDSVDIS6": {
        "WDEONDSVDIS6": {
            "id": "urn:vcloud:vm:7ed43492-a7ce-4963-b5bb-5ec2ca89477c",
            "name": "WDEONDSVDIS6",
            "description": "",
            "dateCreated": "2021-04-13T13:44:29.973+0000"
        }
    },
    "WDEONASVSTR0": {
        "WDEONASVSTR0": {
            "id": "urn:vcloud:vm:7afa34fe-b239-4abe-90df-3f270b44db1f",
            "name": "WDEONASVSTR0",
            "description": "",
            "dateCreated": "2021-03-10T16:17:50.947+0000"
        }
    },
}

I need extract only fields id, name and description to create a csv with them. I test this but the output file is in blank:

$pathToJsonFile = x
$pathToOutputFile = x

$obj = Get-Content $pathToJsonFile -Raw | ConvertFrom-Json
print $obj
$obj | select id, name, description | Convertto-csv > $pathToOutputFile

Upvotes: 0

Views: 598

Answers (2)

jompa
jompa

Reputation: 46

Edit: Mathias R. Jessens answer is better written than this, i would do it that way instead of how i posted.

Okay so i copied the json you posted, imported it. Since each array of information is stored like this

"WDEONDSVDIS6": {

   "WDEONDSVDIS6": {

i used get-member to iterate each of the arrays and then select the info from that. Also, you dont need to use Convertto-csv > $pathToOutputFile, use the export-csv command instead. Below is my code how i would have done it, there is probably a better way but this works :)

$pathToOutputFile = x.csv
$obj = Get-Content example.json -Raw| ConvertFrom-Json

$obj2 = ($obj | Get-Member -MemberType noteproperty).Name
$result = foreach($item in $obj2){

    $obj.$item.$item | select id,name,description
}
$result | Export-Csv -Path $pathToOutputFile -Encoding utf8 -NoTypeInformation

Upvotes: 1

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174575

You'll need to "discover" the parent property names (eg. 'LPEONASVVAP0') via the psobject hidden memberset. Since the outer and inner properties are named the same, we can re-use the name to get the inner property value:

$obj.psobject.Properties |ForEach-Object {
  $_.Value.$($_.Name)
} |Select id,name,description |Export-Csv -NoTypeInformation -Path $pathToOutputFile

Upvotes: 1

Related Questions