xml to custom csv conversion using powershell

Question

for the following xml :

<references bit="-1" pointname="1-ANN-01_CMD">
    <function drop-id="1" function-id="01A7" number="103" originating="1" title="HR-1 ANNUNCIATION-4" unit-id="1" />
</references>
<references bit="-1" pointname="1-ANN-01_KB">
    <function drop-id="2" function-id="01A7" number="104" originating="0" title="HR-1 ANNUNCIATION-5" unit-id="1" />
    <function drop-id="3" function-id="01B7" number="105" originating="0" title="HR-1 ANNUNCIATION-DUPLICATE" unit-id="1" />
</references>
<references bit="-1" pointname="test">
    <function drop-id="5" function-id="01A7" number="107" originating="1" title="HR-1 ANNUNCIATION-4" unit-id="1" />
    <function drop-id="6" function-id="01A8" number="108" originating="0" title="HR-1 ANNUNCIATION-5" unit-id="1" />
</references>

the powershell script that i am using is

$MainXmlFile=([xml]"<root>$(gc BigShtFile.xml)</root>").root.references | % {
foreach ($item in $_) {
    $Obj = New-Object Object


    Add-Member -InputObject $Obj -MemberType NoteProperty -Name PName -Value $_.pointname | ? {originating -eq "0"}
    if ($_.function.originating -eq 0) {
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name Drop   -Value ($_.function | ? -Property originating -EQ 0 | Select -Property "drop-id" -Exp "drop-id")
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name SHEET  -Value ($_.function | ? -Property originating -EQ 0 | Select -Property number -Exp number)
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name DWG   -Value ($_.function | ? -Property originating -EQ 0 | Select -Property "function-id" -Exp "function-id")
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name TITLE  -Value ($_.function | ? -Property originating -EQ 0 | Select -Property title -Exp title)
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name UNIT   -Value ($_.function | ? -Property originating -EQ 0 | Select -Property "unit-id" -Exp "unit-id")
   } else {                                                            
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name Drop  -Value $_.function."drop-id" 
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name SHEET -Value $_.function.number
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name DWG -Value $_.function."function-id"
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name TITLE -Value $_.function.title
    Add-Member -InputObject $Obj -MemberType NoteProperty -Name UNIT -Value $_.function."unit-id"
           }
    #Add-Member -InputObject $Obj -MemberType NoteProperty -Name BIT -Value $_.bit
    $Obj
  }
}
$MainXmlFile | Format-Table -AutoSize | Export-Csv Output.csv -NoTypeInformation

and the output that i am getting is :-

whereas the desired output should contain 4 rows in which the entry 1-ANN-01-KB should be repeated twice with the subsequent information in single row. Please help.

"whereas the desired output should contain 4 rows". Nope. According to your sample, it shouldn't. — David Brabant
– David Brabant, Commented Apr 6, 2017 at 13:30
You will need to loop over the function tags, in a similar way to how you are looping over the reference tags. At the moment it is handling the 2nd reference tag in one go. — G42
– G42, Commented Apr 6, 2017 at 13:34
@DavidBrabant what i mean from the desired output is, if you look at the second line in the output it is like (1-ANN-01_KB {2,3} {104,105} ). whereas what is desired is 1-ANN-01_KB 2 104 and in the next line 1-ANN-01_KB 3 105 — n00b
– n00b, Commented Apr 6, 2017 at 13:43
@gms0ulman thanks for pointing it out. Could you please elaborate a little bit more. — n00b
– n00b, Commented Apr 6, 2017 at 13:45
When the second <references> is processed, $item.function.GetType() reports that it is a System.Array. — lit
– lit, Commented Apr 6, 2017 at 14:13

G42 · Accepted Answer · 2017-04-06 14:23:19Z

I wasn't able to add this to your code; I'm not used to that approach. Please see below for an approach using . indexing like you have in some parts of your code. You can replace the parts within the $obj hashtable where the values are assigned if that is required.

$MainXmlFile=([xml]"<root>$(gc BigShtFile.xml)</root>").root.references | % {
    foreach ($item in $_) {

        if($item.function.originating -eq 0){

            # the second loop, which is missing from your original code
            foreach ($func in $item.function){

                # casting as an array is essential otherwise you will get an error:
                # Method invocation failed because [System.Management.Automation.PSObject] does not contain a method named 'op_Addition'.

                [array]$obj += New-Object psobject -Property @{
                    PName = $item.pointname
                    Drop  = $func."drop-id"
                    SHEET = $func.number
                    DWG   = $func."function-id" 
                    TITLE = $func.title
                    UNIT  = $func."unit-id"
                }
            }

        }else{

            <# I'm not sure what behaviour you're expect this else to perform.
             # So I'm leaving here for completeness for now
            foreach ($func in $item.function){
                [array]$obj += New-Object psobject -Property @{
                    PName = $item.pointname
                    Drop  = $func."drop-id"
                    SHEET = $func.number
                    DWG   = $func."function-id" 
                    TITLE = $func.title
                    UNIT  = $func."unit-id"
                }
            }
            #>
        }

    }
}

# Use Format-Table to output to host. Don't use it or any of the Format- functions with Export-CSV; they won't do what you want
$obj |  Format-Table -AutoSize

# A PSObject will export without order by default. TO get the order you want, use Select and the required columns
$obj | Select-Object PName, Drop, SHEET, DWG, TITLE, UNIT | Export-Csv Output.csv -NoTypeInformation

Output: (to screen, unordered. CSV is ordered)

Collectives™ on Stack Overflow

xml to custom csv conversion using powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related