VI replacing 1 character between two double quotes

Question

I have a 200,000+ line file that I need to update.

My issue is that I need to use commas to separate the fields; however in the original file there are commas placed in the "comment section." The comment section is delimited by "". Not all lines have this comment field populated and it may not contain any commas.

So when using awk with -F, my columns are skewed due to the commas in the comment section.

Here is an example of a line in my file:

XX,YYY,ZZZ,XXXX,Y,,,QQQQ,AAAA,BBBBB,,,XXX,YYY,,,,,ZZZ,QQ,AAAAAA,YY,ZZZZZZZZ,"Zona Industriale, Via Golgi snc",QQ.ZZZZZZZZ,QQ.ZZZZZZ,31.5,2,0,MACRO,#N/A

In the above I need to replace: "Zona **Industriale, Via Golgi snc" with "Zona **Industriale Via Golgi snc"

So long story short the , needs to be removed between the ""s in the line above and in about 70% of the existing file.

Answer 1

I copied your example line in a test and found a search and replace command that will find your commas within quotes and delete them.

%s/".*\zs,\ze.*"/

The breakdown:

• %s/ for performing the search
• " for the beginning of your comment
• .* for any number of any characters
• \zs for the command to start the match
• , for the comma in question
• \ze for the command to end the match
• .* for any number of any characters
• " for the ending of your comment
• / and nothing after the slash because you just want it deleted.

It worked for me, let me know if it doesn't work for you.

Update:

After doing some more searching, it looks like this is the correct answer for multiple replacements:

%s/"\([^"]|""\)*"/\=substitute(submatch(0), ",", " ", "g")/g

Which searches for everything between quotes, and then calls the substitute command to submatch 0 which is everything contained in the previous search command, the next argument is the string to search for, the third argument is what to replace it with, and I believe the last argument is to make it global for the file.

Taken from this previous vi/vim question.

Answer 2

If all the lines have at most one "" field, you could try

global/"/normal! f"f,x

and repeat (@: the first repeat, then @@ for the rest, with a count if you like) until the commas are gone.

But I would really suggest using an actual CSV parser; e.g., python's pandas library could do something like

data = pandas.read_csv(..., names=[...])
data['comments'].apply(lambda f: f.replace(',', ''))
data.to_csv(...)

Thought at that point, you might as well admit you cannot parse a CSV with awk -F,…