0

I have a csv file I would like to remove any rouge " or , in it. The problem is all my fields look something like this "***********","*********", where * = can be any character including , and ". I need to remove any , or " that might appear between the delimiters "," so my strings will remain in double quotes.

"This, has a comma","This has "quotes"",",but what if this happens "too""

ideally the output should be

"This has a comma","This has quotes","but what if this happens too"

I tried this, but it removed all my double quotes

awk 'BEGIN{FS=OFS=","} {for (i=1; i<=NF; i++) {gsub("\"","", $i)}}1' input.csv > output.csv

edit: Tried aborruso solution on this sample input row.

"33333","23","01-07-31-0-000-026.001","   ","","55540","COUNTY ROAD","","","","COUNTY ROAD","","Y","","","VILLE","YY","33367","","","4.246","-22.331","3","X","6000      ","","134","","000003"," "," ","New New New 
 New","000000370","0000004111","0000002111","94111"," ","JOHN SMITH","BILL","E BILLY",""," ",", JANE DOE","DOE","BOB","","BILL E BILLY BOB",", JANE DOE JOHN DOE BABY DOE"," ","","313 MAIN BLVD","313","","","MAIN","BLVD","","","","NEW YORK","NY","48555","2335","Z005"," ","1234","0","0","12345","12345","0","12345","1234","    ","1234","","01","",""," "," "," "," "," "," "," "," "," "," "," "," ","0","","0","","","","","","","","0","0","0","0","0","","","","","","","","","1","","","","","","0","","","","","","","","","","","0","0","","","  ","       ","          ","       ","       ","01","      ","DISTRICT 9 THE MOVE","","       ","          ","DISNEY NETFLIX","","COME ADD 1/4 OF A CUP 1/4 OF A SPOON 13-T00Z-B32C; THE 524.01'(X)350'(Y)TO W R/W OF SO SI'LLY 987.7';WOULDN'T YOU SAY","24313317","Z",""

The output I get is like this

"(error)","(error)","01-07-31-0-000-026.001","   ","","(error)","COUNTY ROAD","","","","COUNTY ROAD","","Y","","","VILLE","YY","(error)

it's just a snippet but you get the idea.

4
  • Your awk is close :) take a look echo '"This, has a comma","This has "quotes"",",but what if this happens "too""'|awk 'BEGIN{FS=OFS="\",\""};{printf "\"";for (i=1;i<=NF;i++) {gsub(/[,"]/,"",$i);printf $i;if (i!=NF){printf OFS};};};END{print "\""}' "This has a comma","This has quotes","but what if this happens too"
    – Paulo
    May 5, 2021 at 16:13
  • doesn't seem to work if I run into something like this ,"string",""," ",", STRING"," will out put an extra , in the end. This is what I tried awk 'BEGIN{FS=OFS="\",\""};{printf "\"";for (i=1;i<=NF;i++) {gsub(/[,"]/,"",$i)}}1' bad.csv
    – Alex
    May 6, 2021 at 21:53
  • This new input line isn't formatted as the example you posted, so it would need another type of filter. I think you got the idea about double and single quotes when running a short awk script in command line. To avoid that problem you can put the script into a file and call it with awk -f file.
    – Paulo
    May 6, 2021 at 22:20
  • Your start text, is not a CSV. To be a CSV it should be in this way gist.github.com/aborruso/b7c464f50e4bc18b89492dfce3910e11/…
    – aborruso
    May 7, 2021 at 7:17

3 Answers 3

0

You can use here (GNU sed) 4.7 :

sed 's/","/\x0/g;s/^"\|"$/\x1/g;s/[",]//g;s/\x0/","/g;s/\x1/"/g' input.csv
  • Replace field separator "," by NUL char (\x0)
  • Replace " at the biginning and at the end by SOH char (\x1)
  • Remove all " and ,
  • Restore field separator and "
3
  • I edited the original post. Please let me know if you are able to help in any way, thank you.
    – Alex
    May 7, 2021 at 20:53
  • You don't tell what you get with my sed proposal.
    – ctac_
    May 7, 2021 at 21:05
  • It works for most cases, but one of the last rows gives an error. It says there's an extra column, but I can't find it yet. When I find it I'll update, thank you.
    – Alex
    May 11, 2021 at 17:21
0

It's not awk, but I think it could be useful for you

If you use Miller and run

echo '"This, has a comma","This has ""quotes""",",but what if this happens ""too"""'| \
mlr --csv -N --quote-all cat then put -S 'for (k in $*){$[k]=gsub($[k],"(,|\")","")}'

you have

"This has a comma","This has quotes","but what if this happens too"

Edit

You have inserted you input text. It's very strange, it's something like

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
33333 23 01-07-31-0-000-026.001 55540 COUNTY ROAD COUNTY ROAD Y VILLE YY 33367 4.246 -22.331 3 X 6000 134 000003 New New New
New 000000370 0000004111 0000002111 94111 JOHN SMITH BILL E BILLY , JANE DOE DOE BOB BILL E BILLY BOB , JANE DOE JOHN DOE BABY DOE 313 MAIN BLVD 313 MAIN BLVD NEW YORK NY 48555 2335 Z005 1234 0 0 12345 12345 0 12345 1234 1234 01 0 0 0 0 0 0 0 1 0 0 0 01 DISTRICT 9 THE MOVE DISNEY NETFLIX COME ADD 1/4 OF A CUP 1/4 OF A SPOON 13-T00Z-B32C; THE 524.01'(X)350'(Y)TO W R/W OF SO SI'LLY 987.7';WOULDN'T YOU SAY 24313317 Z

Running the mlr command you have this CSV ouput

"33333","23","01-07-31-0-000-026.001","   ","","55540","COUNTY ROAD","","","","COUNTY ROAD","","Y","","","VILLE","YY","33367","","","4.246","-22.331","3","X","6000      ","","134","","000003"," "," ","New New New 
 New","000000370","0000004111","0000002111","94111"," ","JOHN SMITH","BILL","E BILLY",""," "," JANE DOE","DOE","BOB","","BILL E BILLY BOB"," JANE DOE JOHN DOE BABY DOE"," ","","313 MAIN BLVD","313","","","MAIN","BLVD","","","","NEW YORK","NY","48555","2335","Z005"," ","1234","0","0","12345","12345","0","12345","1234","    ","1234","","01","",""," "," "," "," "," "," "," "," "," "," "," "," ","0","","0","","","","","","","","0","0","0","0","0","","","","","","","","","1","","","","","","0","","","","","","","","","","","0","0","","","  ","       ","          ","       ","       ","01","      ","DISTRICT 9 THE MOVE","","       ","          ","DISNEY NETFLIX","","COME ADD 1/4 OF A CUP 1/4 OF A SPOON 13-T00Z-B32C; THE 524.01'(X)350'(Y)TO W R/W OF SO SI'LLY 987.7';WOULDN'T YOU SAY","24313317","Z",""
11
  • Thank you I tried that and I'm getting "(error)","(error)" in some columns... any idea why that is? It looks like it's always the first two than the others I can't find a pattern.
    – Alex
    May 6, 2021 at 22:08
  • If you copy and paste my example, do you have that error?
    – aborruso
    May 7, 2021 at 4:19
  • Please copy and paste here your input text (one example row)
    – aborruso
    May 7, 2021 at 4:20
  • Please check original post I made an edit with the information you were asking for.
    – Alex
    May 10, 2021 at 14:04
  • 1
    it's ok, I already coded something up in Java that took care of everything for me. I appreciate you helping so much. It's been a really valuable learning experience. I didn't even know about miller until you shared it, thank you so much!
    – Alex
    May 11, 2021 at 20:38
0

in GNU awk one could use FPAT delimiter specification, than it would correctly handle delimiters inside quotes, but at the cost of some performance of course:

awk 'BEGIN{ FPAT="([^,]*)|(\"[^\"]*\")"; OFS="," } { for (i=1; i<=NF; i++) {gsub("\"|,","", $i)}; print $0; }' input.csv > output.csv
awk 'BEGIN{ FPAT="([^,]*)|(\"[^\"]*\")"; OFS="|" } { $1=$1; print $0; }' input.csv > output2.csv

where FPAT regex mean

  • ([^,]*) - any string not containing , (delimiter) or
  • (\"[^\"]*\") - any string between quotes \", not having another quote [^\"] inside
  • for (i=1; i<=NF; i++) {gsub("\"|,","", $i)}; - replaces " or , in field values and rebuilds record with OFS
  • alternatively, use $1=$1 is to rebuild the record, using the current OFS with delimiter | and then work with file as pipe-separated.

P.S. you can replace delimiter , with ; or also * with + to avoid empty field values.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .