regex to remove comma between double quotes notepad++

Question

I am trying to remove commas inside double quotes from a csv file in notepad++, this is what I have:

1070,17,2,GN3-670,"COLLAR B, M STAY","2,606.45"

and I need this:

1070,17,2,GN3-670,"COLLAR B M STAY","2606.45"

I ma trying to use notepad find/replace option with a reg exp. pattern. I tried all kind of combination but didn't manage to do :( The file contains 1 million rows.

After whole today I am not anymore sure if a simple regex can do? Maybe I should go with a script...python?

Answer 1

mrki, this will do what you want (tested in N++):

Search: ("[^",]+),([^"]+")

Replace: $1$2 or 12

How does this work? The first parentheses capture the beginning of the string up to (but not including) the comma into Group 1. The second parentheses capture the end of the string after the comma into Group 2. The replacement substitutes the string with a concatenation of Group 1 and Group 2.

In more detail: in the first parentheses, we match the opening double quotes then any number of characters that are not a comma. That is the meaning of [^,]+. In the second parentheses, we match any number of characters that are not a double quote with [^"]+, then the closing double quotes .