使用Powershell在json文件中将列值替换为null [英] Replace column value with null in json file using Powershell

问题描述

这是我的json文件的样子：

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"
                 ]
             ]
}

我要替换列中的所有值注释为null，然后使用powershell转换为csv文件。请帮助实现这一目标。

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.

我希望将列名作为标题，将行作为在csv中用逗号分隔的行。
我的输出csv文件应如下图所示：

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

推荐答案

要将这个json转换为CSV文件并不难。

只需加载JSON，将其转换为对象并遍历属性即可构建新对象数组另存为CSV：

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

生成的CSV文件：

"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"

这篇关于使用Powershell在json文件中将列值替换为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！