Regex in Notepad++ to find lines without exactly 9 pipes

Question

I have a CSV that I need to clean up before reading it into a table. It is a pipe-delimted, 10-column structure. The challenge is, in some records, the 9th column has carriage returns. I need to replace those CRLF instances with a string of symbols (as place holders) such as #$%, but I only need to replace the CRLFs that are within the 9th column, for each record.

So in this example:

Susan|Ward    |1     |1          |1     |1     |0     |||3250905     
Allen|Doe|4     |1          |1     |1     |0     ||
some text
text
more text in the same column|3250061     
Mary|James|4     |1          |1     |1     |0     |||3250061     Albert|Nordling|1     |1          |1     |0     |0     |||900434      
Henry|Johnson|1     |1          |1     |0     |0     |||900434      
Tony|Anderson    |1     |1          |1     |1     |0     |||3250905     

The records for Susan, Henry and Tony all have 9 pipes, but Allen's has CRLFs instances (in Notepad++) that need to be replaced to "pull" them all up to the same line to get that record all on one line.

Additionally, Mary's and Albert's records are both on one line together. I need to be able to find those as well so I can split them.

So a regex to find lines that do not have exactly 9 pipes would do the trick.

I can find the records with the 9th column on multiple lines with this regex:

^[^|]*$

But I can't figure out how to find all lines that do not have 9 pipes (10 columns). This won't work:

^[^|{9}]*$

What would do this?

One specific point: If at all possible, I need to be able to have the search select an entire block of lines (which would be each record) that don't have 9 pipes. So in my example, the line that starts with Allen, a search in Notepad++ would highlight that line and the following 3 lines. The regex ^[^|]*$ would select all three lines after the Allen line, as that's looking for lines without pipes at all.

Answer 1

You can use this expression to match lines with exactly 10 columns:
^([^|\n]*\|){9}[^|\n]*$
You can test it here

Where:

• ^ ... $ match beginning and end of a line
• [^|\n]* matches content of a column (no | and no new line; can even be empty)
• \| matches pipe (need to be escaped

Matching lines with less then nine columns:
^([^|\n]*\|){0,8}[^|\n]*$

Matching lines with less then nine columns:
^([^|\n]*\|){10,1000}[^|\n]*$

Update

Playing around a bit this seems to work to find rows with not exactly 10 columns (9 |):
(^([^|\r\n]*\|){0,8}[^|\r\n]*$)|(^([^|\r\n]*\|){10,1000}[^|\r\n]*$)
Test it here