Export-csv issue

PowerShell Help
1

Hi All,

I’m trying to use the below to output my foreach loop into a CSV. However it’s creating the CSV with length property, rather than the actual properties in my foreach loop.

I think it’s because my foreach loop isn’t presenting any objects for export-csv to output. Can someone help me with another way of doing this.

 

Thanks in advance.

$vnetscsv = "vnets.csv"
$vnets = Get-AzVirtualNetwork

$vnetexport=foreach($vnetin$vnets)


{


$vnet.Name 
$vnet.AddressSpace.AddressPrefixes



}


$vnetexport | export-csv -path “$csvlocation$vnetstxt”

2

You’re outputting simple data; Export-Csv is designed to work with complex objects, as it traverses their properties to build the table. Each row in a CSV is considered one object. The following example illustrates how this works:

PS> [PSCustomObject]@{Property1 = $true; Property2 = 10; Property3 = "Text"} | ConvertTo-Csv
"Property1","Property2","Property3"
"True","10","Text"

Bare strings have only one property as such; their length. Their actual data isn’t part of their set of properties.

To get the output you’re looking for, you need to be giving the CSV cmdlets an object with named properties, similar to above. In your case, you could do either of the below:

# Using [PSCustomObject]
$Vnetexports = foreach ($vnet in $vnets) {
    [PSCustomObject]@{
        Name            = $vnet.Name 
        AddressPrefixes = $vnet.AddressSpace.AddressPrefixes
    }
}

# Using Select-Object
$Vnetexports = $vnets | Select-Object -Property Name, @{
    Name       = 'AddressPrefixes'
    Expression = {$_.AddressSpace.AddressPrefixes}
}

Note that in the second case, because the latter property is a bit “buried”, so to speak, you have to tell Select-Object exactly where it is and what to call it in the end.

3

Thanks Joel.

That makes a lot of sense.

However when I run that, I get the correct VNet name which is perfect, however for the addressspace I get this - System.Collections.Generic.List`1[System.String]

 

Thanks,

4

Yeah, looks like the address space has multiple values, then. That’s a bit problematic, as CSV is a format designed for one-cell-per-column, it’s just a very basic spreadsheet really.

Converting to JSON instead would get you the data in a more complete form, but if it has to be CSV you may want to edit that property and put all the values into a single box with something like this instead:

$Vnetexports = $vnets | Select-Object -Property Name, @{
    Name       = 'AddressPrefixes'
    Expression = {$_.AddressSpace.AddressPrefixes -join ', '}
}
5

Hi Joel,

 

That’s worked a treat. Thank you :slight_smile: