CSVファイル中のカラム数が異なるレコードをLinux上で手軽に検出する

目的

CSVファイルの中にある、不正なレコード（カラム数違い）を、できるだけ手軽に検出、修正する

動機

データの初期投入とかで、CSVからデータをインポートしたいが、ファイルの編集ミスやら、
エスケープ漏れなどで、うまくインポートができなくなってしまうことがある。
こういうとき、プログラムの改修やらログレベルの変更やらに頼らずに、簡単なコマンドだけで
不正なレコードを検出して、vimとかでちゃちゃっと直してしまいたい。

アプローチ

行ごとのカラム数を数えて、カラム数が異なる行を探す

結論

CSVのレコードがすべて正しい（カラム数が同一）ときは、下記のコマンドで「正しいこと」は確認できる

$ awk -F, '{print NF}' < sample.csv | uniq | wc -l
-> 1

このコマンドの実行結果が1であればすべてのカラム数が同じ。2以上なら、何かしら不正なレコードがあることがわかる。

例えば、下記のように、3行目に不正なレコード（カラム数が5）を含んでいるCSVファイルだと、結果は１にならない。

1,aaa,bbb,ccc
2,ddd,eee,fff
3,ggg,hhh,iii,jjj
4,kkk,lll,mmm

$ awk -F, '{print NF}' < sample.csv | uniq | wc -l
-> 3

この時、何行目が不正なレコード、つまり、カラム数が4でないレコードであるかを知るには、
下記のコマンドを実行すればいい。

$ awk -F, '{print NF"::"NR}' < sample.csv | grep -v '^4::'
-> 5::3

⇒ 出力が、 '5::3' と出たので、3行目に、カラム数5のレコードが存在していることがわかる。

レコードも一緒に見たい場合は、$0 も出力してやる

$ awk -F, '{print NF"::"NR, $0}' < sample.csv | grep -v '^4::'
-> 5::3 3,ggg,hhh,iii,jjj

解説（やってること）

awk の F オプションにカンマを指定することで、awk に渡す文字列のデリミタをカンマに変えることができる。
そのため、awk -F, '{print NF}'としてあげると、「カンマ区切りで行を分割し、1行に含まれるフィールドの数を表示する」という動きになるので、
1行毎のカラム数を標準出力に出力してくれる。（ NF は、フィールド数）

カラム数が正しいレコード（カラム数4）であれば、 "4:: " から始まるはずなので、これをgrep -v で
除外してあげれば、不正なレコードの行数だけを取得することができる。

応用編

ダブルクォートでカンマをエスケープしているCSVの場合

こんなCSVの場合

"aaa","bbb","ccc"
"ddd","eee","fff"
"ggg","hhh","iii"
"jjj","kkk","lll,mmm"

デリミタを , ではなく、"," にしてやる。（空レコードもダブルクォートでくくらていることを想定）

$ awk -F'","' '{print NF}' < sample.csv | uniq | wc -l
-> 1

タブ区切り、空白区切りの場合

タブ区切り（tsv）の場合は、ちょっとだけ工夫してあげる必要がある。タブやスペース区切りの場合、空のカラムが存在しているとき、連続スペースを一つのデリミタとして認識してしまうため、正しく数えることができない。

そういう場合は、sedコマンドで一度別の文字に置き換えてからawkに渡してやると、うまくいく。ただし、デリミタにする文字がレコード中にあると、今度は誤検出されてしまうため、
出現する確率が限りなく低い文字（バッククオートとか）にしておきましょう。

$ sed -r 's/\t/``/g' < sample.csv | awk -F'``' {print NF"::"NR} | uniq | wc -l
-> 1