1

One of our business partners is routinely sending us malformed CSV files. The file will contain 50,000 rows and several hundred of them have too many column delimiters.

Is there a way in Notepad++ to find all lines where the count of a particular delimiter is greater or less than a specified number, e.g. count( | ) <> 16

  • 1
    Definitely with some regex, but you'll need to wait for a regexpert to come along. My regex isn't up to spec i'm afraid. – spikey_richie May 13 at 15:03
4
  • Ctrl+F
  • Select "Mark" tab
  • Find what: ^[^|]*(?!(?:\|[^|\r\n]*){16}$)(?:\|[^|\r\n]*)*$
  • check Wrap around
  • check Regular expression
  • UNCHECK . matches newline
  • Mark all

Explanation:

^                   # beginning of line
  [^|]*             # 0 or more any character not pipe
  (?!               # start negative lookahead, make sure we haven't after:
    (?:             # start non capture group
      \|            # a pipe
      [^|\r\n]*     # 0 or more non pipe, non linebreak
    ){16}           # end goup, must appear 16 times
    $               # end of line
  )                 # end lookahead
  (?:               # start non capture group
    \|              # a pipe
    [^|\r\n]*       # 0 or more non pipe, non linebreak
  )*                # end goup, may appear 0 or more times
$                   # ed of line

Screen capture:

For simplicity, I test for number of delimiter <> 3

enter image description here

  • A very helpful answer, thank you especially for the explanations. – Tim May 13 at 21:34

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.