CODEWITHSUNDEEP
xmlpowershellcsv
JasonBluefire
JasonBluefire

Reputation: 311

Powershell: Merge lines with different data in a single CSV document

I am new to Powershell and still learning,

I have a CVS file that looks like this: https://dl.dropboxusercontent.com/u/95418691/example.png

The mac address and vlan go with the line above but I can't seem to find a way to do it, because not every line has a mac address listed. I made this CVS file from a weird XML with this code:

$XML | 
    select-xml -xpath "/SolarWinds_SwitchPortMap/Interfaces/Interface |
    /SolarWinds_SwitchPortMap/Interfaces/Interface/MACs/MAC" | 
    select -ExpandProperty Node | 
    select -Property Interface, Description, MACAddress, ip, DNS, VLAN, Speed | 
    Export-Csv -NoTypeInformation -Path "c:\Users\$env:username\Desktop\$ip.csv"

So either way of fixing this code or writing something else to combine the lines of CVS would be great, and also if would not mind explain your solution, I am trying to learn :) thanks.

Edit: This is what the XML looks like:

 <SolarWinds_SwitchPortMap Layer2Device="<info>">
     <Interfaces>
         <Interface ifIndex="<info>" CollectionTime="<info>" ConnectorPresent="<info>" Duplex="<info>" HardwareType="<info>" ifAdminStatus="<info>" ifAdminStatustest="<info>" Description="*** Frame 1 ***" ifDescr="<info>" MTU="<info>" Interface="<info>" ifOperStatus="<info>" ifSpeed="<info>" ifType="<info>" ifTypeName="<info>" InBitsSec="<info>" InPktsSec="<info>" LastChange="<info>" LastPacketIn="<info>" LastPacketOut="<info>" ModulePortIndex="<info>" OutBitsSec="<info>" OutPktSec="<info>" Port="<info>" TrunkPort="False">
             <MACs>
                 <MAC MACAddress="<info>" IPAddress="" DNS="" VLAN="1" Manufacturer="<info>" /> 
             </MACs>
         </Interface>
         <Interface ifIndex="<info>" CollectionTime="<info>" ConnectorPresent="<info>" Duplex="<info>" HardwareType="<info>" ifAdminStatus="<info>" ifAdminStatustest="<info>" Description="*** Frame 2 ***" ifDescr="<info>" MTU="<info>" Interface="<info>" ifOperStatus="<info>" ifSpeed="<info>" ifType="<info>" ifTypeName="<info>" InBitsSec="<info>" InPktsSec="<info>" LastChange="<info>" LastPacketIn="<info>" LastPacketOut="<info>" ModulePortIndex="<info>" OutBitsSec="<info>" OutPktSec="<info>" Port="<info>" TrunkPort="False">
             <MACs>
                 <MAC MACAddress="<info>" IPAddress="" DNS="" VLAN="1" Manufacturer="<info>" /> 
             </MACs>
         </Interface>
         <Interface ifIndex="<info>" CollectionTime="<info>" ConnectorPresent="<info>" Duplex="<info>" HardwareType="<info>" ifAdminStatus="<info>" ifAdminStatustest="<info>" Description="*** Frame 3 ***" ifDescr="<info>" MTU="<info>" Interface="<info>" ifOperStatus="<info>" ifSpeed="<info>" ifType="<info>" ifTypeName="<info>" InBitsSec="<info>" InPktsSec="<info>" LastChange="<info>" LastPacketIn="<info>" LastPacketOut="<info>" ModulePortIndex="<info>" OutBitsSec="<info>" OutPktSec="<info>" Port="<info>" TrunkPort="False" /> 
         <Interface ifIndex="<info>" CollectionTime="<info>" ConnectorPresent="<info>" Duplex="<info>" HardwareType="<info>" ifAdminStatus="<info>" ifAdminStatustest="<info>" Description="*** Frame 4 ***" ifDescr="<info>" MTU="<info>" Interface="<info>" ifOperStatus="<info>" ifSpeed="<info>" ifType="<info>" ifTypeName="<info>" InBitsSec="<info>" InPktsSec="<info>" LastChange="<info>" LastPacketIn="<info>" LastPacketOut="<info>" ModulePortIndex="<info>" OutBitsSec="<info>" OutPktSec="<info>" Port="<info>" TrunkPort="False">
             <MACs>
                 <MAC MACAddress="<info>" IPAddress="" DNS="" VLAN="1" Manufacturer="<info>" /> 
             </MACs>
         </Interface>
     </Interfaces>
 </SolarWinds_SwitchPortMap>

Upvotes: 0

Views: 165

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200473

Try something like this:

$csv  = "$env:USERPROFILE\Desktop\$ip.csv"
$expr = '/SolarWinds_SwitchPortMap/Interfaces/Interface'

$XML | Select-Xml -XPath $expr | select -Expand Node | % {
  New-Object -Type PSCustomObject -Property @{
    "Interface"   = $_.Interface;
    "Description" = $_.Description;
    "Speed"       = $_.ifSpeed;
    "MACAddress"  = $_.MACs.MAC.MACAddress;
    "IPAddress"   = $_.MACs.Mac.ip;
    "DNS"         = $_.MACs.MAC.DNS;
    "VLAN"        = $_.MACs.MAC.VLAN;
  }
} | Export-Csv $csv -NoTypeInformation

Upvotes: 1

Related Questions