Define fields by content and remove separator inside

Question

I have some comma separated files that unfortunately contain commas inside some of the strings. That makes sorting them non trivial. See previous question.

Regardless of the sorting, I figure it would be best to remove such enclosed commas, because they are only going to be a potential hazard for each programme in my pipeline.

I am only just learning awk/gawk. I figured, a good strategy would be:

1. Define fields by content, not separator, as here.
2. Remove separator inside fields as here, mindful of the modification that is supposed to restrict the gsub to a single column

Then I made the following attempt of a script sorter.awk, which I intended to remove commas only from inside column 6:

BEGIN {
FPAT = "([^,]+)|(\"[^\"]+\")"

}

{gsub(/[,]/,"",$6)}1

But when I use the command

gawk -f sorter.awk bugtest.csv > output.csv

to apply it to the following file, bugtest.csv:

1000,101,1,2,"VEN","Venezuela, Bolivarian Republic of",1967,22,4,99,0,0,1967-12-07,"R/22/2328A",0,1,"PRIVILEGES AND IMMUNITIES","TO ADOPT OPERATIVE PARAG. 2 OF DRAFT RESOL. (A/6965) ON DIPLOMATIC PRIVILEGES AND IMMUNITIES, WHICH PARAGRAPH URGES U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON PRIVILEGES AND IMMUNITIES.",0,0,0,0,0,0,0,22027
1000,713,1,1,"TWN","Taiwan, Province of China",1967,22,4,99,0,0,1967-12-07,"R/22/2328A",0,1,"PRIVILEGES AND IMMUNITIES","TO ADOPT OPERATIVE PARAG. 2 OF DRAFT RESOL. (A/6965) ON DIPLOMATIC PRIVILEGES AND IMMUNITIES, WHICH PARAGRAPH URGES U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON PRIVILEGES AND IMMUNITIES.",0,0,0,0,0,0,0,22027
100,101,1,2,"VEN","Venezuela, Bolivarian Republic of",1948,3,9,6,37,0,1948-11-07,"R/3/566C",0,1,"DISARMAMENT, NUCLEAR","TO ADOPT PARAGRAPH 7 OF THE USSR DRAFT RESOL. (A/723), SAID PARAGRAPH RECOMMENDING THE PROHIBITION OF ATOMIC WEAPONS INTENDED FOR AGGRESSION.",0,1,1,0,0,0,0,3023
1001,101,1,1,"VEN","Venezuela, Bolivarian Republic of",1967,22,1,101,0,0,1967-12-07,"R/22/2328B",0,0,"PRIVILEGES AND IMMUNITIES","TO ADOPT DRAFT RESOL. (A/6965) URGING U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON (DIPLOMATIC) PRIVILEGES AND IMMUNITIES AND DEPLORING ALL DEPARTURES FROM THE RULES OF INTERNATIONAL LAW ON THE SUBJECT.",0,0,0,0,0,0,0,22028
1001,713,1,1,"TWN","Taiwan, Province of China",1967,22,1,101,0,0,1967-12-07,"R/22/2328B",0,0,"PRIVILEGES AND IMMUNITIES","TO ADOPT DRAFT RESOL. (A/6965) URGING U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON (DIPLOMATIC) PRIVILEGES AND IMMUNITIES AND DEPLORING ALL DEPARTURES FROM THE RULES OF INTERNATIONAL LAW ON THE SUBJECT.",0,0,0,0,0,0,0,22028
1002,101,1,3,"VEN","Venezuela, Bolivarian Republic of",1967,22,11,50,51,0,1967-12-07,"R/22/2338A",1,1,"INTERNATIONAL YEAR FOR HUMAN RIGHTS","TO ADOPT THE AMENDMENT (A/L. 542) TO DRAFT RESOL. (A/7008) ON \INTERNATIONAL YEAR FOR HUMAN RIGHTS\\, WHICH AMENDMENT DELETES OPERATIVE PARAG.10.\""""",0,0,0,1,0,0,0,22029

the output.csv looks like this, without any commas:

1000 101 1 2 "VEN" "Venezuela Bolivarian Republic of" 1967 22 4 99 0 0 1967-12-07 "R/22/2328A" 0 1 "PRIVILEGES AND IMMUNITIES" "TO ADOPT OPERATIVE PARAG. 2 OF DRAFT RESOL. (A/6965) ON DIPLOMATIC PRIVILEGES AND IMMUNITIES, WHICH PARAGRAPH URGES U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON PRIVILEGES AND IMMUNITIES." 0 0 0 0 0 0 0 22027
1000 713 1 1 "TWN" "Taiwan Province of China" 1967 22 4 99 0 0 1967-12-07 "R/22/2328A" 0 1 "PRIVILEGES AND IMMUNITIES" "TO ADOPT OPERATIVE PARAG. 2 OF DRAFT RESOL. (A/6965) ON DIPLOMATIC PRIVILEGES AND IMMUNITIES, WHICH PARAGRAPH URGES U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON PRIVILEGES AND IMMUNITIES." 0 0 0 0 0 0 0 22027
100 101 1 2 "VEN" "Venezuela Bolivarian Republic of" 1948 3 9 6 37 0 1948-11-07 "R/3/566C" 0 1 "DISARMAMENT, NUCLEAR" "TO ADOPT PARAGRAPH 7 OF THE USSR DRAFT RESOL. (A/723), SAID PARAGRAPH RECOMMENDING THE PROHIBITION OF ATOMIC WEAPONS INTENDED FOR AGGRESSION." 0 1 1 0 0 0 0 3023
1001 101 1 1 "VEN" "Venezuela Bolivarian Republic of" 1967 22 1 101 0 0 1967-12-07 "R/22/2328B" 0 0 "PRIVILEGES AND IMMUNITIES" "TO ADOPT DRAFT RESOL. (A/6965) URGING U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON (DIPLOMATIC) PRIVILEGES AND IMMUNITIES AND DEPLORING ALL DEPARTURES FROM THE RULES OF INTERNATIONAL LAW ON THE SUBJECT." 0 0 0 0 0 0 0 22028
1001 713 1 1 "TWN" "Taiwan Province of China" 1967 22 1 101 0 0 1967-12-07 "R/22/2328B" 0 0 "PRIVILEGES AND IMMUNITIES" "TO ADOPT DRAFT RESOL. (A/6965) URGING U.N. MEMBER-STATES WHO HAVE NOT YET DONE SO TO ACCEDE TO THE U.N. CONVENTION ON (DIPLOMATIC) PRIVILEGES AND IMMUNITIES AND DEPLORING ALL DEPARTURES FROM THE RULES OF INTERNATIONAL LAW ON THE SUBJECT." 0 0 0 0 0 0 0 22028
1002 101 1 3 "VEN" "Venezuela Bolivarian Republic of" 1967 22 11 50 51 0 1967-12-07 "R/22/2338A" 1 1 "INTERNATIONAL YEAR FOR HUMAN RIGHTS" "TO ADOPT THE AMENDMENT (A/L. 542) TO DRAFT RESOL. (A/7008) ON \INTERNATIONAL YEAR FOR HUMAN RIGHTS\\, WHICH AMENDMENT DELETES OPERATIVE PARAG.10.\" """" 0 0 0 1 0 0 0 22029

So, how to achieve quoted separator to be removed just from column 6? That would be the coma after >Venezuela and Taiwan for clarity.

Answer 1

You just need to set the output field separator, OFS:

BEGIN {
FPAT = "([^,]+)|(\"[^\"]+\")"
OFS = ","
}

{gsub(/[,]/,"",$6)}1

Otherwise, you get the default OFS, which is the space character.

Note that , isn't a regex metacharacter, so doesn't need to be enclosed in brackets on the LHS of the gsub, so this simpler expression would also work:

{gsub(/,/,"",$6)}1