2

I've got a csv file (the column separator is "<;>" ,the text delimiter is the double quote and the row separator "crlf") of more than 18000 lines.

However,many of the data contained within the text separator do contain "crlf" , this create issue when trying to import or validate the file structure as well as other one.

"2"<;>"1305767"<;>"MSCUFH613249 [CRLF] 199PACKAGES"<;>""<;>"Y"[CRLF]

I've tried the suggestions found on the following posts to no avail.

  1. Replace charaters
  2. Replace Carriage Return

I've been able using Notepad++ to remove the Carriage return with the Find\Replace with the Wrap Around and Extented options On. However this also replace the row delimiter at the end of the line.

I would like to replace all "\r\n" or CRLF within any text delimiters("") with a blank space.

Thanks

4

This should help:

\n(?!")|\r(?!")|\r\n(?!")
  • Care to expand on that for the benefit of us non-regular expression buffs? – Tog Dec 12 '13 at 14:15
  • Thanks @Maze Oslo; This will find both "CrLf" ,"Lf" and "Cr" between the double quote ("") text delimiters. I've coupled it with the find and replace. In the find i inserted the above reg-ex and in replace a blank space with the "Wrap around" and "Regular Expression" ON. – Raymond Dec 12 '13 at 14:32
  • 3
    This renders the remaining EOL characters in the Unix/OSX format. You may want to use the EOL Conversion utility in the edit menu to swap back to Windows Format when you're done. – Jason Dec 12 '13 at 15:05
  • Thanks @Jason ,I used find "\s\n(?!" )" and replace with "\r\n" – Raymond Dec 12 '13 at 15:45
2

You can use the following regex search-&-replace-s:

  1. Search-&-replace [CRLF] within "..." with a simple white space:

    Find: "([^"]*)\r\n([^"]*)"

    Replace: "$1 $2"

  2. If required, search-&-replace <;> with an appropriate separator, say, ,:

    Find: <;>

    Replace: ,


Example:

Input:

"2"<;>"1305767"<;>"MSCUFH613249 
 199PACKAGES"<;>""<;>"Y"

Output:

After step-1:

"2"<;>"1305767"<;>"MSCUFH613249   199PACKAGES<;>""<;>"Y"

After step-2:

"2","1305767","MSCUFH613249   199PACKAGES,"","Y"
  • Thanks @Roney, however this make the next line being added to first one. – Raymond Dec 12 '13 at 15:23

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.