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

debugcn 发表于 Dev

液体786

我需要将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

根据

尽管您的示例CSV文件未显示（从Excel复制/粘贴），但我假设在记事本中打开该文件时，它看起来像这样：

“ vmType”，“环境”，“描述”，“函数”，“数据中心”，“ Size”，“ adDomain”，“主机名”，“ VolumeName”，“ VolumeLetter”，“ VolumeSize”，
“ Windows Server”，“ dev “，” VMBuild1“，” app“，” DC1“，” Medium“，” mydomain.com“，” VMBUILD1“，”日志“，” G“，” 10“ 
” Windows Server“，”产品“，” VMBuild2 “，” app“，” DC2“，” Medium“，” mydomain.com“，” VMBUILD2“，” Data“，” E“，” 50“ 
” Windows Server“，” dev“，” VMBuild1“，” app “，” DC1“，”中“，” mydomain.com“，” VMBUILD1“，“脚本”，“ H”，“ 25”

CSV没有用于的列RequestedFor，因此以下代码将其用作硬编码变量。

您需要使用而不是将csv作为字符串数组读取并进行很多拆分和除去引号字符的操作Import-Csv。

之后，剩下要做的就是您要格式化最终JSON的方式。

$requestor = 'John Doe'
$csvData   = Import-Csv -Path 'D:\Test\vmfile.csv'

# get an array of PSObjects
# we use 'Group-Object Hostname' here to allow VMs with multiple extra disks
$allVMs = $csvData | Group-Object Hostname | ForEach-Object {
    $disks = $_.Group | Select-Object VolumeName, VolumeLetter, VolumeSize
    $vm = $_.Group[0] | Select-Object * -ExcludeProperty VolumeName, VolumeLetter, VolumeSize
    $vm | Add-Member -MemberType NoteProperty -Name 'ExtraDisks' -Value @($disks)
    # output the VM object
    $vm
}

# combine the requestor, main element 'VMs' and the objects 
# gathered above into a new object and convert that to JSON
[PsCustomObject]@{
    RequestedFor = $requestor
    VMs          = @($allVMs)
} | ConvertTo-Json -Depth 4

输出：

{
    "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"
                                       },
                                       {
                                           "VolumeName":  "Scripts",
                                           "VolumeLetter":  "H",
                                           "VolumeSize":  "25"
                                       }
                                   ]
                },
                {
                    "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"
                                       }
                                   ]
                }
            ]
}

当然，您可以将其附加| Set-Content -Path 'TheOutputFile.json'到json文件中。

PS PowerShell不会产生“漂亮”的json。如果需要将其转换为适当间距的json，请参阅我的函数Format-Json

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。