We help IT Professionals succeed at work.

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
Comment
Watch Question

SimonPrincipal Analyst

Commented:
= "<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.
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
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++)
}
$xml.Save($XmlTarget)

Open in new window

Author

Commented:
Thanks for the feed back everyone...

For the powershell script for the $path.. would I just input this: "ExcludedItem_"
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
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"):
<root>
	<Whatever>
		<Setting name="ExcludedItem_0" value="3|7|C:\*" />
		<Setting name="ExcludedItem_2" value="3|7|C:\*" />
		<Setting name="ExcludedItem_3" value="3|7|C:\*" />
	</Whatever>
</root>

Open in new window

Author

Commented:
Thanks.... I work but edit all sections of my document.. Could the script be used against a text file?
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
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?

Author

Commented:
Disregard ,my previous post  made a stupid mistake on my end. You script worked perfectly...