CSVの囲み文字とエスケープ文字を置換する

2023年6月4日更新

CSVファイルのデータフィールドに囲み文字が含まれている場合、Excelに取り込もうとすると崩れてしまう。
通常は囲み文字と区別するために、データフィールド内の囲み文字をエスケープしたりする。
このようなCSVを正常に取り込むために、囲み文字を別文字に置換することを考える。
また、囲み文字を別文字に置換した後は、エスケープ文字はもはや不要なので削除する方法を考える。
ここでは、囲み文字のダブルクォーテーションをチルダに置換するものとする。

まず、サンプルCSV (in.csv) の内容を確認。

1$ cat in.csv
2"aaa","bb\"","\\cc"

次に、このサンプルCSV内の "," を ~,~ に置換する。

1$ sed 's/","/~,~/g' in.csv
2"aaa~,~bb\"~,~\\cc"

次に、行頭の " を ~ に置換する処理を追加。

1$ sed -e 's/","/~,~/g' -e 's/^"/~/' in.csv
2~aaa~,~bb\"~,~\\cc"

次に、行末の " を ~ に置換する処理を追加。

1$ sed -e 's/","/~,~/g' -e 's/^"/~/' -e 's/"$/~/' in.csv
2~aaa~,~bb\"~,~\\cc~

なお、上記の行頭・行末の " を ~ に置換する処理を一つに纏めることができる。 その場合の置換コマンドは以下のようになる。
's/(^"|"$)/~/g' ・・・ -E オプションで拡張正規表現を使う場合
's/\(^"\|"$\)/~/g' ・・・ GNU sed で基本正規表現を使う場合

次に、\" のエスケープ文字 \ を削除する処理を追加。

1$ sed -e 's/","/~,~/g' -e 's/^"/~/' -e 's/"$/~/' -e 's/\\"/"/g' in.csv
2~aaa~,~bb"~,~\\cc~

次に、\\のエスケープ文字 \ を削除する処理を追加。

1$ sed -e 's/","/~,~/g' -e 's/^"/~/' -e 's/"$/~/' -e 's/\\"/"/g' -e 's/\\\\/\\/g' in.csv
2~aaa~,~bb"~,~\cc~

これにてExcelなどに取り込むときに、囲み文字として ~ を指定することで、正常に取り込める。

また、以下のようにパイプで繋げても同じ結果を得られる。

1$ sed 's/","/~,~/g' in.csv | sed 's/^"/~/' | sed 's/"$/~/' | sed 's/\\"/"/g' | sed 's/\\\\/\\/g'
2~aaa~,~bb"~,~\cc~

1$ cat in.csv | awk '{for(i=1; i<=10000000; i++){print $0}}' > bigIn.csv

この新サンプル (bigIn.csv) に対して、一つ目の方法で処理時間を計測。

1$ time sed -e 's/","/~,~/g' -e 's/^"/~/' -e 's/"$/~/' -e 's/\\"/"/g' -e 's/\\\\/\\/g' bigIn.csv > /dev/null
2
3real	0m13.180s
4user	0m13.089s
5sys	0m0.089s

二つ目のパイプを使う方法での結果は以下。

1$ time sed 's/","/~,~/g' bigIn.csv | sed 's/^"/~/' | sed 's/"$/~/' | sed 's/\\"/"/g' | sed 's/\\\\/\\/g' > /dev/null
2
3real	0m6.124s
4user	0m20.152s
5sys	0m0.456s

パイプを使った方が少し速い。
パイプを使うと各コマンドはサブシェル実行、すなわちパラレル処理されるので、それが効いたものと思われる。
なお、timeコマンドの表示内容の確認はUNIX/Linuxの部屋 timeコマンドの使い方を参考にした。
このサイトに以下の記述がある。

それぞれの値の関係性はreal = user + sys + I/O 待ち時間である。
ただしマルチコア CPU だと user と sys が real を超える可能性がある。

パイプの方法でuserがrealより大きくなっているのは、マルチコアシステムでパラレル処理が行われることにより、各コアでの所要時間が加算されたためだろうか。

関連ページ

• 正規表現を使ってCSVの全角半角混在する行を特定する
• CSVでカンマセパレータだけを置換する（フィールド内カンマはスルー）