Powershell script to modify XML from a CSV file-CodePudding

I am trying to create a script PowerShell to modify the "Password" value of an XML file node from a CSV file. Each account has a different password in the CSV.

CSV looks like this:

Computer;AccountName;Password
SERVER-01;service.testA;mdp123
SERVER-02;service.testB;mdp456
SERVER-03;service.testC;mdp789

XML file looks like this:

<Configuration>
    <TechnicalAccounts>
        <Account Id="service.testA">
            <Login>service.testA</Login>
            <Description>this is a description</Description>
            <Password>OldPassword</Password>
        </Account>
        <Account Id="service.testB">
            <Login>service.testB</Login>
            <Description>this is a description</Description>
            <Password>OldPassword</Password>
        </Account>
        <Account Id="service.testC">
            <Login>service.testC</Login>
            <Description>this is a description</Description>
            <Password>OldPassword</Password>
        </Account>
    </TechnicalAccounts>
</Configuration>

I am trying to put all the data from the CSV into a tab and then if the "AccountName" matches the "Login" in the XML then it changes the password associated. But nothing of what I've tried is working. Do you have any ideas? Thanks for the help.

CodePudding user response：

You haven't shown in your question what you've already tried (which, for future reference, is not the way things are done on SO). But given that your task isn't simple, you should try something like the below:

Assuming your csv is loaded into $data and your xml into $xml, then:

$results = $data | ConvertFrom-Csv  -Delimiter ';'
#extract the relevant elements from the csv:
$results | ForEach-Object {
    $User = $_.AccountName 
    $Password = $_.Password 
}

#then use xpath to search within the xml for the target elements:
foreach($result in $results) {
    $target = $result.AccountName
    $exp = '//Account[./Login/text()="' $target '"]/Password'
    $node = $xml.SelectSingleNode($exp)
    $node.innerText=$result.Password
}

You can save the output to a new file:

$xml.Save("mynewfile.xml")

CodePudding user response：

After research and help, the code bellow is working for me. I put it here in case it can help others.

#=======================================================================
# Variables
#=======================================================================
$ScriptPath = Split-Path $MyInvocation.MyCommand.Path
$ScriptName = $MyInvocation.MyCommand.Name
$XMLFile    = "FILE.xml"
$CSVFile    = "FILE.csv"
$XMLPath    = "$ScriptPath\$XMLFile"
$CSVPath    = "$ScriptPath\$CSVFile"

#=======================================================================
# Import files
#=======================================================================
$InputData   = Get-Content -Path $CSVPath | ConvertFrom-Csv -Delimiter ";"
$XMLDocument = New-Object -TypeName System.Xml.XmlDocument
$XMLItem     = Get-Item -Path $XMLPath -ErrorAction Stop
$XMLDocument.Load($TechnicalAccountXmlItem.FullName)

#=======================================================================
# Updating XML
#=======================================================================
ForEach ($Data in $InputData)
{
    $Name = $Data.AccountName
    $Password = $Data.Password
    $XPath = '//Account[Login="{0}"]' -f $Name
    $AccountNode = $XMLDocument.SelectNodes($XPath)

    $AccountNode | ?{$_} | ForEach-Object {
        Write-Host ("The password of the account [{0}] has been modified from [{1}] to [{2}]." -f $_.Login,$_.Password,$Password)
        $_.Password = "$Password"
    }
}

$XMLDocument.Save("$ScriptPath\NewFile.xml")