Ok, so this one's actually a little tricky. Arguably, regex isn't the best tool for the job, but it can do it.
-replace "(?<=^((\|[^|]*){5})+)\|","`n|"
I'll try to walk you through it:
- Your text has a section you want to match and a section you want to replace. Traditionally, regex replaces the entire search string, so you would use a capture group to specify some part of the search string to be cloned to the replacement output. Another way is to use a lookaround, which is what I've done here. PowerShell (.NET) is one of the few regex languages that supports variable-length lookbehinds, so we're in luck.
- The
(?<=) section is a lookbehind. That means everything between the = and ) is matched but not replaced. So ^((\|[^|]*){5})+ is used as a condition - the replacement will only happen if this bit matches the text before the intended replacement.
- The
^((\|[^|]*){5})*[^|]* section can be summed up as "from the start of the line (^), match sets of five |s, and then match the text up to the next |".
- The start of the line
^ is important - otherwise it can match anywhere in the line and there's no guarantee of how many |s came before.
- Because
| has a special meaning in regex, it needs to be escaped: \|. It does not need to be escaped when within a character class ([]).
[^|]* means "text up to the next |" — more technically, "as many characters other than | as possible" — more technically "repeat the [^|] character class as many times as possible, where that character class matches any character other than |".* means "zero or more repetitions of the previous character, as many as possible"- So
(\|[^|]*) means match | followed by as many characters as possible up till the next |. This will match |text
{5} means repeat the previous token exactly 5 times. It's exactly equivalent to copy-pasting the preceding token 5 times. So this will match |text|text|text|text|text((\|[^|]*){5})+ is one or more repetitions of that entire group. So it can match |text|text|text|text|text, |text|text|text|text|text|text|text|text|text|text, etc. - in multiples of 5. The reason we use + instead of * is we don't want to match the empty group and replace the very first |.- And that makes the entire lookbehind, meaning it will only replace a
| with exactly a multiple of 5 |s behind it, from the start of the line.
- Following that up with a
\| as the actual text to replace, preceded by the matched lookbehind.
Taking your example |STATUS1|HOSTNAME1|IP1|MAC1|IS_WIRED1|STATUS2|HOSTNAME2|IP2|MAC2|IS_WIRED2|STATUS3|HOSTNAME3|IP3|MAC3|IS_WIRED3, it will match the following:
|STATUS1|HOSTNAME1|IP1|MAC1|IS_WIRED1**|**STATUS2|HOSTNAME2|IP2|MAC2|IS_WIRED2**|**STATUS3|HOSTNAME3|IP3|MAC3|IS_WIRED3
You'll notice here (if you haven't already) that you're actually trying to replace every 5th | minus the first, not every 6th. But the lookbehind method handles the "minus the first" situation fairly cleanly.
And now the replacement string.
- Because this is PowerShell, when we want
\n, we actually want `n because the PowerShell escape character is `. Note that this is only necessary in the replacement string; in the regex itself you would still use \n to pass that literal sequence to the regex engine.
- And because you have a leading
| on every line, we need to add a new | after the new line. This works out because your original lines do not end with a |, therefore there is nothing to replace at the end of the lines, therefore we don't end up with an extra new line nor trailing |.
If you prefer the more traditional capture group method:
-replace "((?:[^|]+\|){4}[^|]+)\|","`$1`n|"
Figuring out how this works is left as an exercise to the reader ;) Tip: the $1 backreference has to be escaped (with `) because otherwise PowerShell interprets it as a shell variable.
