Powershell How to export the XML file into Excel (CSV)

Question

I would like to export this xml file into Excel (CSV). I have search online for some examples but I cant seem to find anything which can hep me.

I'm not good with powershell.



  
    
      2
      256
      434
    
    
      13.107
      13.14/24
    
    
      *.google
      *.yahoo
      *.some other link
    
  
  
    
      *.rtrt
      eever
    
    
      23.178
      23.18
      23.19
    
    
      2a01:13::/4
      2a01:1

This is what I have wrtten but its not giving what I need.

[xml]$file = get-content ./O365IPAddresses.xml
$xmlProperties = $file.SelectNodes("/products/product")

    Foreach ($xmlProperty in $xmlProperties) {
        $o = New-Object Object

        Add-Member -InputObject $o -MemberType NoteProperty -Name Name -Value $xmlProperty.name

        foreach ($p in $xmlProperty.addresslist)
        {
            $type += $p.type
            $add += $p.address
        }

        Add-Member -InputObject $o -MemberType NoteProperty -Name Type -Value $type
        Add-Member -InputObject $o -MemberType NoteProperty -Name Address -Value $add

        $type="";
        $add="";

        #Add-Member -InputObject $o -MemberType NoteProperty -Name Name -Value $xmlProperty.name
        $o
    }

    $o="";

This is what i need the code to output.

Name    Type    V
Office  IPv4    12111,12121,12,12,1,2,12,1,2,
Office  IPv6    12111,12121,12,12,1,2,12,1,2,
Office  URL google, yahoo
lyo IPv4    12111,12121,12,12,1,2,12,1,2,
lyo IPv6    12111,12121,12,12,1,2,12,1,2,
lyo URL some lyn, yahoo

FoxDeploy · Accepted Answer

You were on the right track, but doing the Add-Member stuff will lead you into failure. PowerShell is an object oriented language, so it's much better to just emit and object as you go through a chunk of code, and let PowerShell collect them all for you in the pipeline.

I've amended and truncated your code. With this code:

$xmlProperties = $file.SelectNodes("/products/product")
$o = New-Object Object
    Foreach ($xmlProperty in $xmlProperties) {

        foreach ($p in $xmlProperty.addresslist)
        {
            [pscustomobject]@{Name=$xmlProperty.Name;Type=$p.type;Address=$p.address}

        }

    }

You'll get this output:

Name   Type Address                               
----   ---- -------                               
office IPv6 {2, 256, 434}                         
office IPv4 {13.107, 13.14/24}                    
office URL  {*.google, *.yahoo, *.some other link}
LYO    URL  {*.rtrt, eever}                       
LYO    IPv4 {23.178, 23.18, 23.19}                
LYO    IPv6 {2a01:13::/4, 2a01:1}

Which you can pipe into Export-Csv to make into a spreadsheet.

I want to draw attention to the [pscustomobject] notation. This is a PowerShell v3 and up shorthand for creating a new object, which accepts a key-value pair of object property and values. So, at this point in our for-each loop, we've got the variable $p defined, which has the following values:

type address              
---- -------              
IPv6 {2a01:13::/4, 2a01:1}

We're making a new object here, grabbing the .Name property of the parent object $xmlProperty, and then picking out the two extra values from $p we wanted to bring over as well.

Hope this helps.

What to do about System.String[]

If one of your properties contains more than one values, then you'll get a weird output in your CSV file. Essentially, PowerShell will scrape through the output, and rerender it in comma separated value format. When a property has two values, PowerShell will list it as System.String[] or the full object name with [] appended at the end, which is notation for an array (object containing more than one item).

$xmlProperties = $file.SelectNodes("/products/product")
$o = New-Object Object
Foreach ($xmlProperty in $xmlProperties) {

    foreach ($p in $xmlProperty.addresslist)
    {
        [pscustomobject]@{Name=$xmlProperty.Name;Type=$p.type;Address=$p.address}

    }

} | select Name, Type, @(N={Address};exp={$_.Address -join ','}}

Powershell How to export the XML file into Excel (CSV)

Answers (1)

What to do about System.String[]

Related Questions