json - 尝试将 CSV 转换为特定的 JSON 格式

Question

我需要将 CSV 转换为特定的 JSON 格式，但遇到了麻烦。

我目前已经创建了下面的 powershell 代码，该代码采用 CSV 文件，其中包含多列和每列的数据

enter code here $csvcontent = get-content "C:\tmp\vmfile.csv" | select -Skip 1
$Json =foreach($line in $csvcontent){

$obj = [PSCustomObject]@{
    description = ($line -split ",")[0] -replace "`""
    requestedFor = ($line -split ",")[1] -replace "`""
    VMs = @{
    vmType = $(($line -split ",")[5] -replace "`"");
    environment = $(($line -split ",")[6] -replace "`"");
    vmdescription = $(($line -split ",")[7] -replace "`"");
    function = $(($line -split ",")[8] -replace "`"");
    datacenter = $(($line -split ",")[9] -replace "`"");
    Size = $(($line -split ",")[10] -replace "`"");
    adDomain = $(($line -split ",")[11] -replace "`"");
    Hostname = $(($line -split ",")[12] -replace "`"")
    }
    ExtraDisks = @{
    VolumeName = $(($line -split ",")[14] -replace "`"");
    VolumeLetter = $(($line -split ",")[15] -replace "`"");
    Size = $(($line -split ",")[16] -replace "`"")
    }
}

$obj | ConvertTo-Json

} 

$json -replace '(?<=:\s+){','[ {' -replace '(?<="\s+)}','} ]'

然后生成以下 json 文件，这不是我需要的，因为我希望它全部位于 VM 括号下，每个 VM 没有单独的

enter code here

{
"requestedFor":  "John Doe",
"VMs":  {
            "Size":  "Medium",
            "datacenter":  "DC1",
            "environment":  "dev",
            "adDomain":  "mydomain.com",
            "vmType":  "Windows Server",
            "vmdescription":  "VM Build1",
            "function":  "app",
            "Hostname":  "VMBuild1"
        },
"ExtraDisks":  {
                   "VolumeLetter":  "G",
                   "Size":  "10",
                   "VolumeName":  "Logs"
               }
  }
 {
   "requestedFor":  "John Doe",
   "VMs":  {
            "Size":  "Medium",
            "datacenter":  "DC2",
            "environment":  "prod",
            "adDomain":  "mydomain.com",
            "vmType":  "Windows Server",
            "vmdescription":  "VM Build2",
            "function":  "app",
            "Hostname":  "VMBuild2"
        },
"ExtraDisks":  {
                   "VolumeLetter":  "E",
                   "Size":  "50",
                   "VolumeName":  "Data"
               }

}

但我需要它看起来像这样

enter code here 
{
"requestedFor":  "John Doe",
"VMs": [ {
    "vmType": "Windows Server",
    "environment": "dev",
    "description":  "VMBuild1",
    "function": "app",
    "datacenter": "DC1",
    "size": "Medium",
    "adDomain": "mydomain.com",
    "Hostname": "VMBuild1",
            "ExtraDisks": [ {
        "VolumeName": "Logs",
        "VolumeLetter": "G",
        "VolumeSize": 10
        }
    ]
    },
    {
    "vmType": "Windows Server",
    "environment": "prod",
    "description":  "VMBuild2",
    "function": "app",
    "datacenter": "DC2",
    "size": "Medium",
    "adDomain": "mydomain.com",
    "Hostname": "VMBuild2",
            "ExtraDisks": [ {
        "VolumeName": "Data",
        "VolumeLetter": "E",
        "VolumeSize": 50
        }
    ]
    }
    ]
    }

这是 CSV 文件内容

     vmType environment description function    datacenter  Size    adDomain    Hostname    VolumeName  VolumeLetter    VolumeSize
     Windows Server dev VMBuild1    app DC1 Medium  mydomain.com    VMBUILD1    Logs    G   10
     Windows Server prod    VMBuild2    app DC2 Medium  mydomain.com    VMBUILD2    Data    E   50

标签： jsonpowershellcsv

Answer 1

您不需要自己解析 csv。这就是ConvertFrom-Csv/Import-CSV的用途。

这就是我的做法。

$CSVObj = get-content "C:\tmp\vmfile.csv" -Raw | ConvertFrom-Csv
$CSVObj | ConvertTo-Json | Set-Content "C:\tmp\vmfile.json"

就这样 ！

但让我们更进一步。您的问题中没有 CSV 样本，因此人们可能会认为输出 JSON 可能仍然不正确。您将如何确保获得您想要的格式？

通过从导入的对象创建一个全新的对象结构，然后将其导出。

这是一个简单的表达方式。

$CSVObj = get-content "C:\tmp\vmfile.csv" -Raw | ConvertFrom-Csv

# Create a new object from $csvObj that you will then export to csv
$Output = foreach ($item in $CSVObj) {
    [PSCustomObject]@{
        Requester = $item.requestedFor
        VMs       = $item.VMs
        Count     = $item.VMs.Count
    }
}

$output | ConvertTo-Json | Set-Content "C:\tmp\vmfile.json"

然后，您将成功修改要输出的 json 以满足您的需求。