如何使用SED替换CSV指定列中的""为"？

Got it, let's solve this problem properly. Your goal is to adjust the extra trailing double quotes only in the 8th column (specifically after product names like "GAUZE PACKING STRIPS 1/4" or "ACE WRAP 3") without touching other instances (like empty "" fields in other columns).

Why Your Original sed Command Failed #

The command sed 's/[[:alnum:]]""//g' is too broad—it matches any alphanumeric character followed by two double quotes, which is why it was deleting unintended content like numeric values paired with "". We need a way to target only the 8th column.

Solution 1: Use awk (Recommended for CSV Column Operations) #

awk is perfect here because it lets us split the CSV into columns and modify only the 8th one, avoiding accidental changes elsewhere. Here's the command:

BEGIN {
    FS = "\",\""  # Split columns using the CSV's quote-wrapped comma delimiter
    OFS = "\",\"" # Use the same delimiter for output to keep CSV structure
}
{
    # Target and replace trailing "" in the 8th column with a single "
    sub(/""$/, "\"", $8)
    
    # Re-add the leading and trailing quotes needed for valid CSV formatting
    print "\"" $0 "\""
}

Run it on your file like this:

awk -f fix_quotes.awk input.csv > output.csv

If you want to target only the specific product names (instead of any 8th column ending with ""), use this more precise variation:

BEGIN {
    FS = "\",\""
    OFS = "\",\""
}
{
    # Check if the 8th column matches either product with extra trailing quotes
    if ($8 ~ /^GAUZE PACKING STRIPS 1\/4""$/ || $8 ~ /^ACE WRAP 3""$/) {
        # Remove one trailing quote to fix the formatting
        $8 = substr($8, 1, length($8)-1)
    }
    print "\"" $0 "\""
}

How This Works #

• FS = "\",\"" splits each line into columns by the "," separator, automatically stripping the surrounding quotes from each column value for easy editing.
• sub(/""$/, "\"", $8) replaces any trailing "" in the 8th column with a single " (adjust to sub(/"$/, "", $8) if you want to remove the trailing quote entirely).
• print "\"" $0 "\"" adds back the leading and trailing quotes needed to keep the CSV valid.

Example Output #

For your first input line:

"ABC-DEF-d98263","12345678","176568981","","588","ABC-DEF-11947","","GAUZE PACKING STRIPS 1/4"","","","2019-02-04T19:09:00-05:00","","XXX","XXX","2019-02-12T23:57:48-06:00","XXX-XXX-176568981"

The output will be:

"ABC-DEF-d98263","12345678","176568981","","588","ABC-DEF-11947","","GAUZE PACKING STRIPS 1/4"","","","2019-02-04T19:09:00-05:00","","XXX","XXX","2019-02-12T23:57:48-06:00","XXX-XXX-176568981"

(Note: The 8th column now has a single trailing quote instead of two, while all other fields remain unchanged.)

Key Advantage of awk #

Unlike sed, which operates on the entire line, awk isolates columns, so you never have to worry about accidentally modifying quotes in other parts of the CSV (like empty fields or numeric entries).

内容的提问来源于stack exchange，提问作者Rituraj Golawar