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.