1

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.

3

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

  • Sorry if I wasn't clear in my explanation - I need to find lines that do not have exactly 9 pipes (10 columns) - whether they have 9 or less or 11 or more columns - can I do that in one regex? So really I need the opposite of what your first regex does... – marky Feb 17 '17 at 14:40
  • Combining them didn't work - it highlighted each successive row each time I hit "Find next". – marky Feb 17 '17 at 14:50
  • Please see my update, it works now. – Máté Juhász Feb 17 '17 at 14:58
  • That does, indeed work to select each single line that does not have 9 pipes. One regex I tried (in my post) selects the entire section that does not have pipes at all. Would it be possible to make yours do the same, so as to select the entire record? So in my sample, Allen Doe and the next three lines would all be selected on a search. Possible? – marky Feb 17 '17 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.