PostgreSQLのロゴ

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

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

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

pgAdmin4からCSVをインポート
pgAdmin4からCSVをインポート

お、エラーでコケた。

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

pgAdmin4のCSVインポートエラー
pgAdmin4のCSVインポートエラー

Linux風のコマンドで確認

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

head yellow_tripdata_2016-12.csvCode language: Bash (bash)
CSVファイルのカンマをheadで確認
CSVファイルのカンマをheadで確認

確かに、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)
CSVファイルの改行コードをcatで調べる
CSVファイルの改行コードをcatで調べる

実行してみると、ヘッダーの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」で入力することに注意。

hat Mでcatしてみる
hat Mでcatしてみる

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)
headコマンドで確認
headコマンドで確認

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

再びpgAdmin4からインポート

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

pgAdmin4からCSVファイルをインポート
pgAdmin4からCSVファイルをインポート

OKボタンで実行すると、

pgAdmin4からCSVファイルをインポート
pgAdmin4からCSVファイルをインポート

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

No responses yet

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です