0

I am struggling a little bit. The file I need to manipulate is a csv with semicolon as field delimiter. In some datasets a comment was added which INCLUDES a semicolon. Luckily these comments begin and end with " .

Example:

;;;"sometext many words or few ; more text; even more text";;;;;;

I need to replace all semicolons between the ", but keep the text. Thanks for helping me out.

Sample dataset:

Before Regex find & Replace:  
Fieldtitles: f1;f2;f3;f4;f5;f6;f7;f8;f9;f10;f11  
Dataset1: ;;text;text;;text;text;text;text;text;text  
Dataset2: text;"text text text ; text text";text;text;text;text;text;text;text;text;text

After Regex find & Replace:  
Fieldtitles: f1;f2;f3;f4;f5;f6;f7;f8;f9;f10;f11  
Dataset1: ;;text;text;;text;text;text;text;text;text  
Dataset2: text;"text text text _ text text";text;text;text;text;text;text;text;text;text  
  • Please share sample from original file and expected output. – Sandeep May 8 '18 at 13:46
  • If your goal is to load the csv file into excel, edit the csv file with notepad, and add the following row to the start: sep=; now you can load it into excel without any problems. – LPChip May 8 '18 at 13:51
  • @LPChip: Thank you, this works for excel-display. But I need to normalize for DB import, table has 150k rows, would prefer a solution which uses RegEx F&R – Sven May 8 '18 at 16:25
  • You may be able to get it normally in excel first, then search/replace in there. I know, this is not an answer, but its a workaround, and if it's a one-time only, it may be your escape. – LPChip May 8 '18 at 18:44
1
  • Ctrl+H
  • Find what: "[^";]*\K;(?=[^";]*")
  • Replace with: _
  • check Wrap around
  • check Regular expression
  • Replace all

Explanation:

"           : a double quote
[^";]*      : 0 or more any character that is not a double quote or a semicolumn
\K          : forget all we have seen until this position
;           : a semicolumn
(?=         : start lookahead, make sure we have, after current position,
  [^";]*    : 0 or more any character that is not a double quote or a semicolumn
  "         : a double quote
)           : ed lookahead

Replacement:

_       : an underscore

Result for given example:

Fieldtitles: f1;f2;f3;f4;f5;f6;f7;f8;f9;f10;f11  
Dataset1: ;;text;text;;text;text;text;text;text;text  
Dataset2: text;"text text text _ text text";text;text;text;text;text;text;text;text;text    
| improve this answer | |
0

In the Replace window, specify the following:

  • Find What: \"(.*?);(.*?)\"
  • Replace with: "\1_\2"
  • Check the Regular Expression radio bullet.

This will replace ; with _ in comments that include one semicolumn. Regex breakdown:

  • \" - Start matching from double quotes
  • (.*?); - match everything until the first semicolumn
  • (.*?)\" - match everything until the next double quotes
  • "\1_\2" - output the matched double quotes and matched subexpressions #1 and #2, while replacing ; with _.

If it's possible to have multiple semicolons in a comment, just keep hitting Replace All (with the Wrap around option checked) - it will replace one semicolon per comment at a time, until all semicolons are replaced.

| improve this answer | |

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.