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

Community
  • 1
Raymond
  • 123
  • 1
  • 8

2 Answers2

4

This should help:

\n(?!")|\r(?!")|\r\n(?!")
Jesse T-Cofie
  • 166
  • 2
  • 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"
Roney Michael
  • 1,056
  • 1
  • 14
  • 22