Getting data from XML and sending to CSV

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of PB PB 10 months, 1 week ago.

  • Author
    Posts
  • #34536
    Profile photo of PB
    PB
    Participant

    I have a text file (parts.txt) that contains a list of part names e.g.

    00199
    00200
    00201

    I then have an XML file that contains these part names and from this file, for each part name I need to extract the values for price1, price2 and price3. Extract from the top of the XML file included below.

    Once these values have been extracted I need to send the total for each price1-3 to a CSV file.

    Any help appreciated.

  • #34555
    Profile photo of random commandline
    random commandline
    Participant

    This will export the part name and a price total for each.

    # Get Part Numbers
    $partnumbers = Get-Content .\parts.txt
    # Get XML file
    [xml]$productxml = Get-Content .\product.xml
    
    # Get details for each partnumber
    $partsobj = {@()}.Invoke()
    foreach ($num in $partnumbers){
     $Total = $productxml.Product.parts.ChildNodes | Where-Object {$_.part_name -eq $num} | Select-Object price* | Measure-Object -Property * -Sum | 
     Measure-Object -Property Sum -Sum
     $price = [pscustomobject]@{'Part Number'=$num;Total=$Total.Sum}
     $partsobj.Add($price)
     } # End Foreach
    $partsobj | Export-Csv .\partstotals.csv -NoTypeInformation -Append
    
  • #34609
    Profile photo of PB
    PB
    Participant

    Hi, this works a treat but I've just found out that there are a number of the XML files created with an older format. The differences in the XML format are:
    price1=cost
    price2=rrp
    price3=margin

You must be logged in to reply to this topic.