CODEWITHSUNDEEP
jsonpowershellcsv
sboivin
sboivin

Reputation: 13

Powershell - parsing through JSON to create CSV

I am extracting a JSON from Facebook, this is what it looks like:

pressions_by_paid_non_paid_unique/day
{
  "data": [
    {
      "name": "page_impressions_by_paid_non_paid_unique",
  "period": "day",
  "values": [
    {
      "value": {
        "total": 549215,
        "unpaid": 549215,
        "paid": 0
      },
      "end_time": "2017-06-02T07:00:00+0000"
    },

What I would like is to create a CSV that would either looks like this:

total,unpaid,paid,endtime
549215,549215,0,2017-06-02T07:00:00+0000

or

value,num,end_time

total,549215,2017-06-02T07:00:00+0000

unpaid,549215,2017-06-02T07:00:00+0000

paid,0,2017-06-02T07:00:00+0000

What I came up with is:

$file = "D:\BI_LP\001-Solutions_Sources\script\Powershell\curl\facebook21.json"
Get-Content $file -Raw |
ConvertFrom-Json | 
Select -Expand data |
Select -Expand values | % {
$end_time = $_.end_time
$value = $_.value
$_.value | select @{n='end_time';e={$end_time}}, @{n='value';e={$value}}
}

Which gives me: 

end_time                 value  

--------                 -----   


2017-05-21T07:00:00+0000 @{total=608837; unpaid=608837; paid=0}

2017-05-22T07:00:00+0000 @{total=682090; unpaid=682090; paid=0}

2017-05-23T07:00:00+0000 @{total=885274; unpaid=885274; paid=0}

2017-05-24T07:00:00+0000 @{total=810845; unpaid=810845; paid=0}

2017-05-25T07:00:00+0000 @{total=755453; unpaid=755453; paid=0}

2017-05-26T07:00:00+0000 @{total=629096; unpaid=629096; paid=0}

Does anyone have any suggestions?

Upvotes: 1

Views: 132

Answers (3)

sboivin
sboivin

Reputation: 13

Thanks Charlie, you got me in the right direction :)

$file = "D:\BI_LP\001-Solutions_Sources\script\Powershell\curl\facebook21.json"
Get-Content $file -Raw |
ConvertFrom-Json | 
Select -Expand data |
Select -Expand values | % {
$end_time = $_.end_time
$total    = $_.value.total
$unpaid   = $_.value.unpaid
$paid     = $_.value.paid
$_.value | select @{n='end_time';e={$end_time}}, @{n='total';e={$total}},@{n='unpaid';e={$unpaid}},@{n='paid';e={$paid}}
}

Upvotes: 0

Adamar
Adamar

Reputation: 311

Based on your code:

@"
{
    "data": [
    {
        "name": "page_impressions_by_paid_non_paid_unique",
        "period": "day",
        "values": [
        {
            "value": {
            "total": 549215,
            "unpaid": 549215,
            "paid": 0
        },
        "end_time": "2017-06-02T07:00:00+0000"
        }
        ]
    }
    ]
}
"@ | ConvertFrom-Json | Select -Expand data | Select -Expand values | % {
    $end_time = $_.end_time
    $value = $_.value
    $_.value | select @{n='endtime'; e={$value.total}}, @{n='unpaid'; e={$value.unpaid}}, @{n='paid'; e={$value.paid}}, @{n='end_time';e={$end_time}}
} | ft -autosize

Gives the following output:

endtime unpaid paid end_time                
------- ------ ---- --------                
 549215 549215    0 2017-06-02T07:00:00+0000

And to export to csv, change the ft -autosize to Export-Csv -Path c:\...

FYI: Your json sample is missing a lot of closing brackets.

Upvotes: 1

MisterSeajay
MisterSeajay

Reputation: 4659

I think you are almost there. You could try creating the output object a bit like this in the foreach loop:

[pscustomobject]@{
  total    = $_.value.total
  unpaid   = $_.value.unpaid
  paid     = $_.value.paid
  end_time = $_.end_time
}

Upvotes: 3

Related Questions