求可移除逗号分隔CSV文件中多余逗号的sed/awk命令或Shell脚本

我有一个逗号分隔的CSV文件，所有字段值均用“”包裹。样例记录如下：

Sample record 1:

“1234”,”0”,”1”,2012-12-01 11:38:00”,”Dear ABC thank you for ,your, email.
If you have any further query please contact us.

Thanks & Regards,

John

Services abc company”,”Null”,”1”,”John”

Sample record 2:

“789”,”0”,”1”,2012-09-01 11:38:00”,”Dear XYZ thank you for “your”, email.
If you have any further query please contact us.

Thanks & Regards,

Max

Services department abc company”,”Null”,”1”,”Max”

我尝试过以下sed命令，但未达预期：

sed -e ‘s/\([^”]\)”,\([^”]\)/\1~\2/g’ -e ‘s/\([^”]\),\([^”]\)/\1~\2/g’ file.csv

其中第一部分想处理记录2中的“your”,模式，第二部分处理记录1中的,your,模式，但结果不对。

期望移除所有字段内的多余逗号，得到如下输出：

Output record 1:

“1234”,”0”,”1”,2012-12-01 11:38:00”,”Dear ABC thank you for your email.
If you have any further query please contact us.

Thanks & Regards

John
Services abc company”,”Null”,”1”,”John”

Output record 2:

“789”,”0”,”1”,2012-09-01 11:38:00”,”Dear XYZ thank you for “your” email.
If you have any further query please contact us.

Thanks & Regards

Max

Services department abc company”,”Null”,”1”,”Max”

需要用sed/awk命令或read line的Unix脚本解决该问题。

方案1：awk状态机处理 #

核心思路是跟踪是否处于引号内，仅删除引号内部的逗号，保留字段分隔用的逗号：

awk '{
    in_quote = 0
    new_line = ""
    for (i=1; i<=length($0); i++) {
        c = substr($0, i, 1)
        if (c == "“" || c == "”") {  # 若文件用英文引号，替换为""
            in_quote = !in_quote
            new_line = new_line c
        } else if (c == "," && in_quote) {
            continue  # 引号内的逗号直接跳过
        } else {
            new_line = new_line c
        }
    }
    print new_line
}' file.csv

方案2：sed循环替换 #

利用sed的分支循环，反复匹配引号内的逗号并删除：

sed -E ':loop
s/(“[^”]*),([^”]*”)/\1\2/g
t loop' file.csv

注：若文件使用英文引号，将脚本中的“和”替换为"即可。

方案3：bash逐行处理脚本 #

用bash逐字符遍历，标记引号状态并过滤内部逗号：

#!/bin/bash

while IFS= read -r line; do
    in_quote=0
    result=""
    for ((i=0; i<${#line}; i++)); do
        char="${line:i:1}"
        if [[ "$char" == "“" || "$char" == "”" ]]; then
            in_quote=$((1 - in_quote))
            result+="$char"
        elif [[ "$char" == "," && $in_quote -eq 1 ]]; then
            continue
        else
            result+="$char"
        fi
    done
    echo "$result"
done < file.csv

使用方式：保存为clean_csv.sh，执行chmod +x clean_csv.sh后运行./clean_csv.sh。

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