extra data after last expected columnエラー

PostgreSQLでCSVをCOPYしようとすると

前回の記事でPostgreSQLの「データの中に引用符のない復帰記号がありました」エラーについて対応法を紹介したが、ニューヨークのタクシーデータをPostgreSQLにインポートしようとするとまたも躓いたのでその対応方法について書いておきたい。

まず、PostgreSQL 12を使っていて、WindowsのバッチファイルからpsqlでCSVファイルをまとめてインポートしようとしたが、エラー多発でうまくいかない。問題の切り分けとしてpgAdmin4から1ファイルだけインポートしてみる。

お、エラーでコケた。

extra data after last expected columnのエラーだ。

Linux風のコマンドで確認

ファイルサイズが大きすぎてExcelでは開くのに時間がかかりすぎるし、Notepad++やメモ帳でも開くことができない。ここはコマンドを使おう。Windowsだが、Git BashがインストールされているのでLinuxのコマンドが使える。headで確認だ。

head yellow_tripdata_2016-12.csvCode language: Bash (bash)

確かに、2行目以降にカンマが2つ「,,」余分に付いてしまっている。他の期間のデータはこういうのはなかったのだが、ニューヨークのタクシーデータは何でこんなに実践的なのだろうか。

sedコマンドで削除したいが、「,,」だと、CSVファイルのある行に空の値が2つ続いた場合も置換されるのは困る。「,,\n」と改行コードを含めて検索して置換するのが良さそう。

さて、改行コードは「\n」か「\r\n」だと思うが、catコマンドとgrepの組合せで見つけてみよう。catコマンドに-Aを付けることで制御コードも表示できる。

cat -A yellow_tripdata_2016-12.csv | grep "\n"Code language: JavaScript (javascript)

実行してみると、ヘッダーの1行目はヒットするのに2行目以降のデータにヒットしない。grepの中身を「\r」や「\r\n」に変更しても同じだ。違う改行コードなのだろうか。

色々と調べた結果、このファイルの改行には「^M$」という記号が使われていた。ハット記号(^)にMだ。

cat -A yellow_tripdata_2016-12.csv | grep "^M"Code language: JavaScript (javascript)

ここで、ハットM(^M)はGit Bash上でキーボードの「Ctrl+V」を押した後、「Ctrl+M」で入力することに注意。

sedコマンドで行末の不要なカンマを取り除く

よし、それでは行末の「,,」を取り除こう。ここでsedコマンドの出番だ。

sed 's/,,^M/^M/g' yellow_tripdata_2016-16.csv > yellow_tripdata_2016-12_v2.csvCode language: Bash (bash)

オプションの意味は、「s」が正規表現で置換、「,,^M」が置換前の文字(不要なカンマ2つと改行)、そして「^M」が置換後の文字(改行)、「g」が全ての行に対して実施、という意味。「-i」オプションを付けると同じファイルに上書きされるが、ここでは別名で保存することにした。

数秒で実行が終わり、headコマンドで確認してみると、

head yellow_tripdata_2016-12_v2.csvCode language: CSS (css)

ちゃんと、行末の不要な「,,」が取り除かれていることを確認できた。

再びpgAdmin4からインポート

それでは、再びpgAdmin4からCSVファイルをインポートしてみる。

OKボタンで実行すると、

よし、インポートできた。