CODEWITHSUNDEEP
jsonpowershellexport-to-csv
megha
megha

Reputation: 833

How to exclude data from json file to convert into csv file powershell

I want to convert my json file into csv -

{
  "count": 28,
  "value": [
  {
    "commitId": "65bb6a911872c314a9225815007d74a",
    "author": {
    "name": "john doe",
    "email": "[email protected]",
    "date": "2020-06-09T17:03:33Z"
     },
    "committer": {
    "name": "john doe",
    "email": "[email protected]",
    "date": "2020-06-09T17:03:33Z"
    },
    "comment": "Merge pull request 3 from dev into master",
    "changeCounts": {
    "Add": 6,
    "Edit": 0,
    "Delete": 0
     },
     "url": "https://dev.azure.com/",
     "remoteUrl": "https://dev.azure.com/"
     },
    {
     "commitId": "bdcb4a1e1e671c15333eacc31aa9795fe32",
     "author": {
     "name": "john doe",
     "email": "[email protected]",
     "date": "2020-06-09T17:03:33Z"
      },
     "committer": {
     "name": "john doe",
     "email": "[email protected]",
     "date": "2020-06-09T17:03:33Z"
     },
     "comment": "Updated FGDH",
     "changeCounts": {
     "Add": 0,
     "Edit": 1,
     "Delete": 0
      },
     "url": "https://dev.azure.com/",
     "remoteUrl": "https://dev.azure.com/"
    }
  ]
 }

I don't want all the fields in my CSV file. I want commitid,commiter.name, commiter.date and comment only.

 Get-Content "\commitinfo.json" -Raw | ConvertFrom-Json | Select -Expand value |Export-Csv 
 "\commitinfo.csv"

with this I get all data , How can I get selected data ?

Another json file -

{
 "value": [
  {
    "id": "5c264dd2-bbcf-4537-8a63-19a0a4d2dc",
    "name": "Develop",
    "url": "https://dev.azure.com/",
    "project": {
    "id": "0042dc5c-fd13-4e3c-bfd7-7feb52e287",
    "name": "test",
    "url": "https://dev.azure.com/",
    "state": "wellFormed",
    "revision": 11,
    "visibility": "private",
    "lastUpdateTime": "2020-04-15T04:04:30.01Z"
    },
    "defaultBranch": "refs/heads/master",
    "size": 55438,
    "remoteUrl": "https://dev.azure.com",
    "sshUrl": "[email protected]:v3",
    "webUrl": "https://dev.azure.com/"
   },
   {
    "id": "3219e8e2-281d-40ad-81c8-1cecf8",
    "name": "automation",
    "url": "https://dev.azure.com/",
    "project": {
    "id": "0e2df786-94a0-42c8-b068-c2656b",
    "name": "automation",
    "url": "https://dev.azure.com/",
    "state": "wellFormed",
    "revision": 19,
    "visibility": "private",
    "lastUpdateTime": "2020-05-02T03:32:50.937Z"
    },
    "size": 0,
    "remoteUrl": "https://dev.azure.com/",
    "sshUrl": "[email protected]:v3",
    "webUrl": "https://dev.azure.com/"
    }
  ]
 }

from this file I only want to select projects with size - 0 , CSV should look like this

     name   size 
     a       0
     b       0

Upvotes: 0

Views: 206

Answers (1)

mklement0
mklement0

Reputation: 438398

Note: Piping the commands below to Export-Csv is omitted for brevity.

You need to use Select-Object with calculated properties:

(Get-Content commitinfo.json -Raw | ConvertFrom-Json).value |
  Select-Object commitid, 
                @{n='committerName'; e={ $_.committer.name } }, 
                @{n='committerDate'; e={ $_.committer.date } }, 
                comment

In the second case, add Where-Object to filter in the objects of interest:

(Get-Content other.json -Raw | ConvertFrom-Json).value |
  Where-Object size -eq 0 |
    Select-Object name, size

Upvotes: 1

Related Questions