☰

CSVでフィールドを分割し、その行のフィールドを新しい行にコピーする方法

tag-icon • tag-icon awk sed csv text-formatting
CSVでフィールドを分割し、その行のフィールドを新しい行にコピーする方法

CSVファイルを使用するターゲットがあり、6番目のフィールドに単語が含まれていますが、最大文字長は16文字です。フィールドが16文字を超える場合は、行をコピーして単語を途切れることなく分離したいと思います。

現在のファイル

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK LMNOP Q RS TUV W XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

希望の出力

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

答え1

GNU Awk( gawk) を使って実行fold行/変数/コプロセスのインポート

gawk -F, '
  BEGIN{
    OFS=FS; 
    cmd="fold -sw 16";
  }

  # if total length (16 + 2 for quotes) is within limit, print as-is
  length($NF) <= 18 {print; next}

  # else
  {
    # trim the quotes, then fold
    print substr($NF,2,length($NF)-2) |& cmd; 
    close(cmd,"to"); 
    NF--; 
    while((cmd |& getline var) > 0){

      # (optional) trim trailing whitespace
      sub(/[ \t]+$/,"",var);

      print $0, "\"" var "\"" ;
    }
    close(cmd,"from");
  }
' file.csv

ジョブからsub末尾のスペースを削除しますfold。

fold -sw17表示された正確な出力を得るには、16文字を末尾のスペースで囲む必要があります（その後削除されます）。ただし、これを行うと、折りたたまれた出力の最後の行が16文字の制限を超える可能性があります。

答え2

私は二重引用符を保持するかなり不都合なawkスクリプトを作成しました。ここに来る:

{
    for ( i=0; i<= length($6); i+=16 )
    {
        if ( i+17 < length($6) )
        {
            if ( i == 0 )
                printf ("%s,%s,%s,%s,%s,%s\"\n", $1, $2, $3, $4, $5, substr($6,i,16))
            else
                printf ("%s,%s,%s,%s,%s,\"%s\"\n", $1, $2, $3, $4, $5, substr($6,i+1,16))
        }
        else
        {
            if ( i == 0 )
                printf ("%s,%s,%s,%s,%s,%s\n", $1, $2, $3, $4, $5, substr($6,i,16))
            else
                printf ("%s,%s,%s,%s,%s,\"%s\n", $1, $2, $3, $4, $5, substr($6,i+1,16))
        }
    }
}

出力は次のとおりです

$ awk -F, -f awks csvfields
"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5"," LMNOP Q RS TUV "
"1","2","3","4","5","W XYZ 12 3456 78"
"1","2","3","4","5","90"
"9","8","7","6","5","LMN O PQ R"
$

唯一の問題は、削除された例とは異なり、境界にスペースがある場合はそのまま残ることです。

答え3

以下のコードを試してください。うまくいきます。

 k=16;for ((j=1;j<=50;j++)); do  awk -v j="$j" -v k="$k" -F "," '{if(length($NF) > 16){print $1,$2,$3,$4,$5,substr($NF,j,k)}else {print $0}}' filename; j=$(($j+16)); done|sort | uniq

出力

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

答え4

SHELL メソッドのみ (Bash および Ksh93 でテスト済み)。しかし、私はこのアプローチが好きですfold。既存のツールを使うからだ。

# read from stdin, output to stdout
# Note no Shebang line at top so it made it easier for to try bash/ksh as interpreters

OIFS="$IFS"
IFS=,
while read f1 f2 f3 f4 f5 f6; do
    f6=${f6#\"}
    f6=${f6%\"}             # strip DQs
    if ((${#f6}<17)); then  # no action
            IFS="$OIFS"
            echo "$f1,$f2,$f3,$f4,$f5,\"$f6\""
            IFS=","
            continue
    else
            IFS="$OIFS"
            while ((${#f6}>17)); do
                    n6=${f6:0:16}
                    f6=${f6#$n6}
                    n6=${n6# }
                    n6=${n6% }
                    echo "$f1,$f2,$f3,$f4,$f5,\"$n6\""
            done
            echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""
    fi
    IFS=","
done
IFS="$OIFS"
exit

結果：

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

usingなどを使用せずに単語分割の問題を解決するには、fold上記のコメント付き行を次のコードに置き換える必要があります。また、2番目のechoコマンドラインを次に置き換えます。

                    c6="$f6"
                    n6=""
                    while (((${#n6}+${#nw})<=16)); do
                            n6=$n6${c6%% *}\
                            n6=${n6# }
                            eval c6=\${c6\#${c6%% *} }
                            nw=${c6%% *}
                    done
                    #n6=${f6:0:16} ### replace by above

交換して

            echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""

そして

            ((${#f6}>0)) && echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""

空のフィールドを避けるために、残りは6です。

次のテストファイルを使用します。

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK LMNOP Q RS TUV W XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
"1","2","3","4","5","A BB CCC DDD EEEE FFFFF GGGGGG HHHHHHH"

結果：

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
"1","2","3","4","5","A BB CCC DDD"
"1","2","3","4","5","EEEE FFFFF"
"1","2","3","4","5","GGGGGG HHHHHHH"

ただし、既存のツールはfold使いやすく、既存のシンプルなツールに基づくUNIXの哲学に従います。しかし、シェルプログラミングが好きなら、上記は解決策を得る1つの方法です。コードの説明が必要な人がいる場合は、私に連絡してください。

関連情報