Replace text in quotes with Regex or AWK

user_13524899 0
Viewed 186

I have a CSV file like this:

Name,Age,Pos,Country
John,23,GK,Spain
Jack,30,"LM, MC, ST",Brazil
Luke,21,"CMD, CD",England

And I need to get this:

Name,Age,Pos,Country
John,23,GK,Spain
Jack,30,LM,Brazil
Luke,21,CMD,England

With this expression I can extract the field but I don't know how to update it in the dataset

grep -o '\(".*"\)' file.csv | cut -d "," -f | sed 's/"//'
sed awk regex
2 Answers
$ sed -E 's/"([^,]+)[^"]*"/\1/' ip.txt
John,23,GK,Spain
Jack,30,LM,Brazil
Luke,21,CMD,England
  • -E to enable ERE
  • " match double quote
  • ([^,]+) match non-comma characters and capture it for reuse in replacement section
  • [^"]*" any other remaining characters
  • \1 will refer to the text that was captured with ([^,]+)

Note that this will work only one double quoted field and won't work if there are other valid csv formats like escaped double quotes, newline character in field, etc

user_40820520

Could you please try following, this should cover case when you have more than 1 occurrence of "....." in your Input_file, written and tested with GNU awk.

awk -v FPAT='[^"]*|"[^"]+"' '
BEGIN{
  OFS=""
}
{
  for(i=1;i<=NF;i++){
    if($i~/^".*"$/){
      gsub(/^"|"$|[, ].*/,"",$i)
    }
  }
}
1
'  Input_file
user_58665800
Related