Update XMl based off CSV values using Powershell

Question

I have a CSV file with the values as below:

Age , Status

29 ,    0
41 ,    1
44,     1
27,     0
60,     1

The XML is as below:

<office>
  <staff branch="Torrance" Type="Implementation">
    <employee>
        <Name>Raj Parpani</Name>
        <function>Implementation</function>
        <age>29</age>
    </employee>
    <employee>
        <Name>Kevin Woo</Name>
        <function>Consultant</function>
        <age>41</age>
    </employee>
  </staff>
  <staff branch="Irvine" Type="Operations">
   <employee>
    <Name>David Woo</Name>
    <function>Data</function>
    <age>42</age>
   </employee>
   </staff>
 </office>

If XML age is equal to the age in CSV, I have to append the status attribute for that age from the csv to the employee. I have tried the code as below:

ORIGINAL CODE

$csv = Import-Csv 'C:\Users\rparpani\Desktop\test2.csv' | Select-Object "Age","Status"
$xml = New-Object XML
$xml.Load("C:\Users\rparpani\Desktop\test.xml")

$nodes = $xml.SelectNodes("/office/staff/employee")

Foreach($row in $csv)
{
    foreach($node in $nodes)
    {

    if($node.age -eq  $row.age)
    {
    $node.SetAttribute("status", $row.Status);
    }


    }

}

Can someone please suggest how to change this to do what I want it to do

MODIFIED CODE

    $csv = Import-Csv 'C:\Users\rparpani\Desktop\test2.csv' | Select-Object "Age","Status"
    $xml = New-Object XML
    $xml.Load("C:\Users\rparpani\Desktop\test.xml")

    $nodes = $xml.SelectNodes("/office/staff/employee")


    foreach($row in $csv) {
      foreach($node in $nodes) {
        if ($node.age -eq $row.age) {
          if ($node.status) {
            $node.status.innerText = $row.Status
          }
          else {
            $status = $xml.CreateNode("element", "status", "")
            $status.InnerText = $row.status
            $node.AppendChild($status)
          }
        }
      }
    }

    $xml.Save("C:\Users\rparpani\Desktop\test.xml")

What if there are two employees with same age? You should be comparing with unique variable instead of age that can be same across multiple people — Jawad
– Jawad, Commented Feb 14, 2020 at 0:00
If age 49 has status 1 on CSV, and there are two employees on xml with that age, we would append status 1 to that employee — caliGeek
– caliGeek, Commented Feb 14, 2020 at 0:02
Please do not change the entirety of your question / code. If you need to add something to original post, please add that separately — Jawad
– Jawad, Commented Feb 14, 2020 at 2:10

Jawad · Accepted Answer · 2020-02-14 17:42:30Z

1

Following code does what you are looking for.

You cannot use SetAttribute to create a new element with a value/InnerText. You need to create an element and append it to the node you are on. In your example xml, staff has two attributes, branch and Type that can be updated with SetAttribute method.
If Status already exists, update it with new value.. otherwise create a new element.

CSV Content

Age,Status
29,0
41,1
44,1
27,0
60,1

Script to add attribute to Node `Employee

foreach($row in $csv) {
  foreach($node in $nodes) {
    if ($node.age -eq $row.age) {
      if ($row.status -eq "0") {
        $node.SetAttribute("status", "Hired")
      }
      else {
        $node.SetAttribute("status", "Fired")
      }
    }
  }
}

edited Feb 14, 2020 at 17:42

answered Feb 14, 2020 at 0:32

Jawad

11.4k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

caliGeek Over a year ago

I tried your code but it says innertext cannot be found on object: $node.status.innerText = $row.Status

caliGeek Over a year ago

Cannot set "status" because only strings can be used as values to set XmlNode properties.

caliGeek Over a year ago

your algorithm creates a new node. I just want to add the attribute like "<employee Status="0"> where 0 should be not fired and 1 is fired so output will be "<employee Status="Not Fired">

Jawad Over a year ago

@RajParpani Added the code up top to show you how to add an atribute using a variable. See if this helps

caliGeek Over a year ago

when it reads the status 1, it is suppose to set the status in xml as "hired". When it reads the status 0, it is suppose to set the status in xml to "Fired". Would i have to have another if?

|

Collectives™ on Stack Overflow

Update XMl based off CSV values using Powershell

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related