Powershell script to copy specific text from excel & create xml files?

The first thing to do would be to save the Excel file as a .csv file (make sure to choose .csv comma delimited).

Then you would want to have an xml template. It would look like this:

<?xml version="1.0" encoding="UTF-8"?>
    <SingleAction>
        <Title>Setting Data Group - **##PARAMNAME##**</Title>
        <Relevance>(if exists property "in proxy agent context" then (not in proxy agent context) else true)</Relevance>
        
        code..
        
        code..
        

        <Parameter Name="Data ID">**##PARAMNAME##**</Parameter>
        <Settings>
        lots of other settings
        settings
        setttings
        ..
        ..
    
        <Target>
            ##DATACOLUMNS##
        </Target>

Then we would replace the ##PLACEHOLDERS## with actual data. In this powershell script, I assume that the headers, Data1 and Data2, have been changed to data1 and data2.

$data = Import-Csv .\data.csv
$luTable = @{}

# Create Keys in Lookup Table
$data | % {
    if (!$luTable.ContainsKey("$($_.data2)")) { $luTable["$($_.data2)"] = New-Object System.Collections.ArrayList }
}

$luTable.Keys | % {
    $key = $_ # Store Key
    $data | where data2 -Match $_ | select data1 | % {
        $luTable[$key].Add($_.data1)
    }
}

# Build XML Files

$luTable.Keys | % {
    $key = $_

    $filetext = gc ".\template.xml"
    $filetext = $filetext.Replace("##PARAMNAME##", $key)

    $targets = ""
    
    $luTable[$key] | % {
        $targets += "<DataColumn1>$($_)</DataColumn1>"
    }

    $filetext = $filetext.Replace("##DATACOLUMNS##", $targets)

    sc -Path ".\$($key).xml" -Value $filetext
}

That should do the trick!