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

目的

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

動機

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

アプローチ

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

結論

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

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

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

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

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