如何用Awk命令拆分CSV第11字段生成新行以提升大文件处理效率

解决方案 #

你原来的shell循环效率低是因为每次循环都会调用cut/tr/fold等多个外部命令，还会产生临时文件读写，改用awk单进程处理可以把速度提升几十到上百倍。

基础版（CSV字段无内部逗号） #

awk -F ',' '
{
    # 拼接前10个字段作为公共前缀，可按需调整包含的字段
    prefix = $1","$2","$3","$4","$5","$6","$7","$8","$9","$10
    # 去除第11字段前后的引号
    col11 = substr($11, 2, length($11) - 2)
    # 按每10个字符遍历第11字段
    total_len = length(col11)
    for (i = 1; i <= total_len; i += 10) {
        # 取当前片段的第6~9位共4个字符
        current_part = substr(col11, i + 5, 4)
        # 输出完整行
        print prefix ",\"" current_part "\""
    }
}' 输入文件路径 > 输出文件路径

兼容版（CSV字段内部含逗号） #

如果你的CSV中存在被引号包裹、内部包含逗号的字段，用gawk的FPAT特性正确解析字段：

gawk '
BEGIN {
    FPAT = "([^,]+)|(\"[^\"]+\")"
}
{
    prefix = $1","$2","$3","$4","$5","$6","$7","$8","$9","$10
    col11 = substr($11, 2, length($11) - 2)
    total_len = length(col11)
    for (i = 1; i <= total_len; i += 10) {
        current_part = substr(col11, i + 5, 4)
        print prefix ",\"" current_part "\""
    }
}' 输入文件路径 > 输出文件路径

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