locked
Convertto-CSV can't handle doubles lines in json file RRS feed

  • Question

  • I'm working on a script that pull info from VM's and places it in a json file.
    In case of multiple VHD files or NIC's it will put it as {<value>,<value>}.

    When converting the above to CSV (either directly from the output or after being output to json) it shows it can't handle multiple values per line.
    It will shows as "System.Object[]".

    Oddly enough, if I use https://json-csv.com/ to convert the json output it will nicely place each extra value on a separate line.

    Below is the code that I use, if someone wants to test it:

    Get-VM -ComputerName <node-name> -VMName <vm-name> | ForEach-Object {
        $vmHdd = $_ | Get-VMHardDiskDrive
        $vmNic = $_ | Get-VMNetworkAdapter
        $vmVlan = $_ | Get-VMNetworkAdapterVlan
    
        [PSCustomObject] @{
            'Node' = $node
            'VM Name' = $_.Name
            'Processor' = $_.ProcessorCount
            'Memory' = '{0:N0} MB' -f ($_.MemoryAssigned / 1MB)
            'HDD Path' = $vmHdd.Path
            'NIC' = $vmNic.Name
            'MAC Address' = $vmNic.MacAddress
            'Dynamic MAC' = $vmNic.DynamicMacAddressEnabled
            'MAC Spoofing' = $vmNic.MacAddressSpoofing
            'VLAN ID' = $vmVlan.AccessVlanId
            # 'Uptime' = $_.Uptime
            # 'Notes' = $_.Notes
        }
    } | ConvertTo-Json | Out-File C:\temp\${env:COMPUTERNAME}_$(Get-Date -Format yyyy-MM-dd)_output.json -Append
    
    $content = Get-Content -Path C:\temp\${env:COMPUTERNAME}_$(Get-Date -Format yyyy-MM-dd)_output.json | ConvertFrom-Json
    $content | Export-Csv -NoTypeInformation -Path C:\temp\${env:COMPUTERNAME}_$(Get-Date -Format yyyy-MM-dd)_output.csv

    I know the last two lines can be done as a single line, but I keep getting an error, so this works for now.
    Apart from the "System.Object[]" obviously.

    Following is the CSV file output how the website converts it, and what I would to see using PowerShell.

    "Node","VM Name","Processor","Memory","HDD Path","NIC","MAC","Dyn MAC","VLAN ID"
    "node-name","vm-name","1","10 GB","C:\ClusterStorage\Volume3\vm-name\vm-name_disk_1.vhd","VM vm-name VLAN 84","mac-address","False","84"
    "","","","","C:\ClusterStorage\Volume10\vm-name\vm-name_2.vhdx","VPC5000-vm-name","mac-address","False","2000"

    Hopefully someone has an idea how to deal with this.


    • Edited by Tom Weustink Wednesday, August 14, 2019 10:21 AM
    Wednesday, August 14, 2019 10:21 AM

Answers

  • Maybe not exactly what you want, but this will fix your "System.Object[]' problem. It's generic enough that you needn't know the names of the properties. If you need the format of expanding a single input object (your JSON) into multiple objects (rows in a CSV) you'll have to do some more work.

    $j = '{
        "Node":  "node-name",
        "VM Name":  "vm-name",
        "Processor":  3,
        "Memory":  "8.192 MB",
        "HDD Path":  [
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_1.vhd",
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_2.vhdx"
                     ],
        "NIC":  [
                    "VM vm-name VLAN 123",
                    "VPC1234-vm-name"
                ],
        "MAC Address":  [
                            "001122334455",
                            "AABBCCDDEEFF"
                        ],
        "Dynamic MAC":  [
                            false,
                            false
                        ],
        "MAC Spoofing":  [
                             1,
                             1
                         ],
        "VLAN ID":  [
                        123,
                        1234
                    ]
    }'
    
    $s = ConvertFrom-Json -InputObject $j
    $h = @{}
    $s.PsObject.Properties | 
        ForEach-Object {
            if ($_.TypeNameOfValue -eq 'System.Object[]'){
                $h.($_.Name) = $_.Value -join ";"
            }
            else{
                $h.($_.Name) = $_.Value
            }
        }
        [PSCustomObject]$h | export-csv c:\temp\whatever.csv -NoTypeInformation
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by Tom Weustink Friday, August 30, 2019 1:02 PM
    Thursday, August 29, 2019 7:43 PM

All replies

  • What does the json look like, or the output from get-vm?  I would loop on the array and repeat the other values.
    • Edited by JS2010 Wednesday, August 14, 2019 2:34 PM
    Wednesday, August 14, 2019 2:32 PM
  • Sorry for the late reply. Some RDP bug passed and then random stuff.

    Here is some output you requested:

    Get-VM till the | ConvertTo-CSV part:

    Node         : node-name
    VM Name      : vm-name
    Processor    : 3
    Memory       : 8.192 MB
    HDD Path     : {C:\ClusterStorage\Volume2\vm-name\vm-name_disk_1.vhd, C:\ClusterStorage\Volume2\vm-name\vm-name_disk_2.vhdx}
    NIC          : {VM vm-name VLAN 123, VPC1234-vm-name}
    MAC Address  : {001122334455, AABBCCDDEEFF}
    Dynamic MAC  : {False, False}
    MAC Spoofing : {Off, Off}
    VLAN ID      : {123, 1234}

    Same as above, but with | ConvertTo-Json:

    {
        "Node":  "node-name",
        "VM Name":  "vm-name",
        "Processor":  3,
        "Memory":  "8.192 MB",
        "HDD Path":  [
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_1.vhd",
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_2.vhdx"
                     ],
        "NIC":  [
                    "VM vm-name VLAN 123",
                    "VPC1234-vm-name"
                ],
        "MAC Address":  [
                            "001122334455",
                            "AABBCCDDEEFF"
                        ],
        "Dynamic MAC":  [
                            false,
                            false
                        ],
        "MAC Spoofing":  [
                             1,
                             1
                         ],
        "VLAN ID":  [
                        123,
                        1234
                    ]
    }


    Thursday, August 29, 2019 2:01 PM
  • Maybe not exactly what you want, but this will fix your "System.Object[]' problem. It's generic enough that you needn't know the names of the properties. If you need the format of expanding a single input object (your JSON) into multiple objects (rows in a CSV) you'll have to do some more work.

    $j = '{
        "Node":  "node-name",
        "VM Name":  "vm-name",
        "Processor":  3,
        "Memory":  "8.192 MB",
        "HDD Path":  [
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_1.vhd",
                         "C:\\ClusterStorage\\Volume2\\vm-name\\vm-name_disk_2.vhdx"
                     ],
        "NIC":  [
                    "VM vm-name VLAN 123",
                    "VPC1234-vm-name"
                ],
        "MAC Address":  [
                            "001122334455",
                            "AABBCCDDEEFF"
                        ],
        "Dynamic MAC":  [
                            false,
                            false
                        ],
        "MAC Spoofing":  [
                             1,
                             1
                         ],
        "VLAN ID":  [
                        123,
                        1234
                    ]
    }'
    
    $s = ConvertFrom-Json -InputObject $j
    $h = @{}
    $s.PsObject.Properties | 
        ForEach-Object {
            if ($_.TypeNameOfValue -eq 'System.Object[]'){
                $h.($_.Name) = $_.Value -join ";"
            }
            else{
                $h.($_.Name) = $_.Value
            }
        }
        [PSCustomObject]$h | export-csv c:\temp\whatever.csv -NoTypeInformation
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by Tom Weustink Friday, August 30, 2019 1:02 PM
    Thursday, August 29, 2019 7:43 PM
  • Thank you! That works very nicely.

    The CSV file will go to a developer who will use that information for more information gathering, so this will do very nicely.

    Now to make it work over a cluster of nodes, but that is doable.

    Friday, August 30, 2019 1:02 PM