Increment Item number within Row

I am updating and XML file and need someway to increment an item number for each row so it is unique. I am used Notepad++ but have tried Excel and notepad can can find no easy way to do this except manually

Here is sample
<Setting name="ExcludedItem_0" value="3|7|C:\*"/>
<Setting name="ExcludedItem_2" value="3|7|C:\*"/>
<Setting name="ExcludedItem_3" value="3|7|C:\*"/>

I need to update the ExcludedItem number and have over 800 lines so i am open to suggestions
LVL 20
Who is Participating?
No. The script queries for nodes named "Setting" and sets the attribute "name".
Again: if all nodes in the the xml file named "Setting" should be processed like this, the existing XPath "//Setting" will do the trick.
If not, $XPath needs to be set to the path with the "Setting" nodes you want to process.
For the following XML, the XPath would need to be "/root/Whatever/Setting" (remember: XML is cAsE sEnSiTiVe, so "root" is not the same as "Root"):
		<Setting name="ExcludedItem_0" value="3|7|C:\*" />
		<Setting name="ExcludedItem_2" value="3|7|C:\*" />
		<Setting name="ExcludedItem_3" value="3|7|C:\*" />

Open in new window

= "<Setting name=" & CHAR(34) & "ExcludedItem_" & ROW() & CHAR(34) & " value=" & CHAR(34) & "3|7|C:\*" & CHAR(34) & "/

Try the above. This just uses ROW() to insert the current row number, but you could equally well refer to a list of numeric values in a separate column.

Note that it uses CHAR(34) to insert all the quote marks, so the quote marks you see in the formula are NOT the quote marks in the results.
XML should be treated as such, so a bit of Powershell should do the trick (source and target files can be the same if you're feeling it's safe to overwrite the original file).
If the only nodes named "Setting" are the ones you want to process, the XPath in the script should work.
If you only want them at a certain level, you need to adjust the $XPath variable accordingly (like "/root/whatever/Setting"; and just in case: keep in mind that XML is case sensitive:
$XmlSource = "C:\Temp\test.xml"
$XmlTarget = "C:\Temp\test2.xml"
$XPath = "//Setting"
$NamePrefix = "ExcludedItem_"
[xml]$xml = Get-Content $XmlSource
$Index = 0
ForEach ($Node In $xml.SelectNodes($XPath)) {
	$Node.SetAttribute("name", $NamePrefix + $Index++)

Open in new window

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

compdigit44Author Commented:
Thanks for the feed back everyone...

For the powershell script for the $path.. would I just input this: "ExcludedItem_"
compdigit44Author Commented:
Thanks.... I work but edit all sections of my document.. Could the script be used against a text file?
What do you mean with "Could the script be used against a text file?"? Do you have several of these xml files, or do you mean textual replacement instead of setting XML attributes?
compdigit44Author Commented:
Disregard ,my previous post  made a stupid mistake on my end. You script worked perfectly...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.