在json中添加方括号，在PowerShell中从CSV中拆分和转换

Question

我有一个场景，我必须分割csv在一定数量的行，每批应该有json文件通过PowerShell脚本生成。

我现在正在做的是:

$csv = "C:Desktopreport.csv"
[int]$totalRows = 0
$reader = New-Object IO.StreamReader $csv
while($reader.ReadLine() -ne $null) { $totalRows++ }
$reader.Dispose()
$totalRows
$startRow = 0
$counter = 1
while($startRow -lt $totalRows)
{   
Import-CSV $csv | Select-Object @{expression = { "Append this value"+ $_.Name}; label = 'NewName'}, @{expression = {$_.Account}; label = 'AccountNumber'} | Select-Object -skip $startRow -first 2 | ConvertTo-Json | Add-Content -Path "C:Desktopr_$($counter).json"

$startRow += 2
$counter++
}

这里唯一的问题是我不能将Account number值括在方括号[]中:

实际:"AccountNumber": "123"期望:"AccountNumber": ["123"]

我也不确定如何把整个json在每个文件下的根元素通过这个。也不确定这个" convert - json "是csv数据需要编辑的方式，请帮助。

这是一个csv供参考-

Name,Account,Role
John,123,Second
Rocky,345,Third
Tony,234,First
Rocky,345,Second
Matt,999,Second
Bernard,888,Third
Matt,999,First
Jacob,789,Second
Angela,777,Second
Jacob,789,First

预期输出

第一个文件:

{   
"details":
[
{
"NewName":  "Append this valueJohn",
"AccountNumber":  ["123","333"]
},
{
"NewName":  "Append this valueRocky",
"AccountNumber":  ["345"]
}
]
}

第二文件:

{   
"details":
[
{
"NewName":  "Append this valueTony",
"AccountNumber":  ["234"]
},
{
"NewName":  "Append this valueRocky",
"AccountNumber":  ["345"]
}
]
}

继续到第6个文件:

{   
"details":
[
{
"NewName":  "Append this valueAngela",
"AccountNumber":  ["777"]
},
{
"NewName":  "Append this valueJacob",
"AccountNumber":  ["789"]
}
]
}
Thanks

Answer 1

继续我的评论，因为我不能在那里使用换行符:

将下面的PowerShell对象转换为Json(JavaScript对象表示法):

@{
details = @{
NewName = 'Append this valueJohn'
AccountNumber = 123
}, @{
NewName = 'Append this valueRocky'
AccountNumber = 345
}
} |ConvertTo-Json

在搜索结果

{
"details": [
{
"NewName": "Append this valueJohn",
"AccountNumber": 123
},
{
"NewName": "Append this valueRocky",
"AccountNumber": 345
}
]
}

如果你想引用值，你需要将它们强制为字符串
(通过使用引号或[String]初始化器):

@{
details = @{
NewName = 'Append this valueJohn'
AccountNumber = "123"
}, @{
NewName = 'Append this valueRocky'
AccountNumber = [String]345
}
} |ConvertTo-Json

结果:

{
"details": [
{
"NewName": "Append this valueJohn",
"AccountNumber": "123"
},
{
"NewName": "Append this valueRocky",
"AccountNumber": "345"
}
]
}

如果您还想将单条目(标量)字符串放在方括号(表示数组)之间，则需要使用数组子表达式操作符@( )或逗号操作符,将值强制到数组中。注意，当涉及多个值时，这种情况会自动发生，例如:AccountNumber = "123", "333"

@{
details = @{
NewName = 'Append this valueJohn'
AccountNumber = @("123")
}, @{
NewName = 'Append this valueRocky'
AccountNumber = ,[String]345
}
} |ConvertTo-Json -Depth 9

^{(同时注意-Depth参数)}
结果:

{
"details": [
{
"NewName": "Append this valueJohn",
"AccountNumber": [
"123"
]
},
{
"NewName": "Append this valueRocky",
"AccountNumber": [
"345"
]
}
]
}

注意，方括号不在默认情况下ConvertTo-Json扩展输出的同一行。您可以考虑使用-Compress参数，但这会将所有放在同一行:

{"details":[{"NewName":"Append this valueJohn","AccountNumber":["123"]},{"NewName":"Append this valueRocky","AccountNumber":["345"]}]}

换句话说，即使外观不同，它们在技术上都是相同的，并且表示相同的对象。