Replace column value with null in json file using Powershell

Question

Here is how my json file looks like :

{
    "count":  12,
    "name":  "Daily Ticket",
    "columnNames":  [
                        "User",
                        "Channel",
                        "Date",
                        "# of Closed Incidents",
                        "Open",
                        "Response",
                        "Remark",
                        "Closed"
                    ],
    "rows":  [
                    [
                     "abc",
                     "Service Web",
                     "\u00272020-06-13 00:00:00\u0027",
                     "1",
                     "0",
                     "0",
                     "this is a text,please replace with null",
                     "1"
                 ],
                 [
                     "xyz",
                     "Email",
                     "\u00272020-06-13 00:00:00\u0027",
                     "21",
                     "1",
                     "0",
                     "this is a text,please replace with null",
                     "7"
                 ]
             ]
}

I want to replace all the values in columns of Remark with null and convert into a csv file using powershell. Please help to achieve this.

I want column names as header and rows as rows separated with comma in csv. My output csv file should look like below one:

User,Channel,Date,# of Closed Incidents,Open,Response,Remark,Closed
abc,Service Web,\u00272020-06-13 00:00:00\u0027,1,0,0,,1
xyz,Email,\u00272020-06-13 00:00:00\u0027,1,0,0,,1

Theo · Accepted Answer · 2020-10-05 11:01:25Z

To convert this json into a CSV file is not that difficult.
Just load the JSON, convert it into an object and loop through the properties building an array of new objects you can save as CSV:

$json = Get-Content -Path 'D:\Test\DailyTicket.json' -Raw | ConvertFrom-Json
$headers = $json.columnNames

$result = foreach ($row in $json.rows) {
    # just a precaution to not run into index errors when there are 
    # more items in the array than there are headers or vice-versa
    $items = [math]::Min($row.Count, $headers.Count)
    # create a new empty (ordered) hashtable
    $hash  = [ordered]@{}
    for ($i = 0; $i -lt $items; $i++) {
        # fill the hashtable, except for iten 'Remark'
        $hash[$headers[$i]] = if ($headers[$i] -ne 'Remark') { $row[$i] } else { $null }
    }
    # If you insist on keeping the apostrophe characters in the date field in unicode format `\u0027`
    # $hash['Date'] = $hash['Date'] -replace "'", '\u0027'

    # output a PSObject to be collected in array $result
    [PsCustomObject]$hash
}

# output on screen
$result | Format-Table -AutoSize

# output to CSV file
$result | Export-Csv -Path 'D:\Test\DailyTicket.csv' -NoTypeInformation

Resulting CSV file:

"User","Channel","Date","# of Closed Incidents","Open","Response","Remark","Closed"
"abc","Service Web","'2020-06-13 00:00:00'","1","0","0",,"1"
"xyz","Email","'2020-06-13 00:00:00'","21","1","0",,"7"

Collectives™ on Stack Overflow

Replace column value with null in json file using Powershell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related