CSVを違うデータ形式のテーブルへコマンド一つでインポート(MySQL)

CSVファイルをデータベースにインポートする際、簡単なプログラムを作ってインポートされる方も少なくはないかと思いますが、私は半々くらいです。

プログラムを作らない場合は、コマンド一つでインポートできるLOAD DATA INFILE構文を使用しますが、最近結構イレギュラーなデータを入れるようになってから、何度もこのオプションを見直すようになってしまったので、一度ここに書き残しておきたいと思います。

CSVの2つの項目を結合したものを1つのカラムへインポート

今回の目的です。
load data infileでぐぐればいくらでも基本的な例は出てきますので、ここではユーザ定義変数を使用し、CSVファイルとテーブルのカラム数がイコールでなかった場合のインポート例を紹介したいと思います。

さらにそれに加え、ユーザ定義関数を使用するのに意味がありそうな、geometry型のカラムに位置情報を格納する構文も入れておきます。

バラバラに書いてしまったのでわかりにくいかと思いますが、つまりは何がしたいのかをまとめると、以下となります。

[CSVファイル]

以下のデータが格納されている。

・住所 (文字列)
・緯度 (XX.XXXXXX)
・経度 (XXX.XXXXXX)

ここでは、CSVファイルパスを仮に /tmp/geodata.csv としておきます。

 
[テーブル]

以下のカラムが用意されているので、上記緯度経度をlatlonカラムへ格納する。

・address (varchar型)
・latlon (geometry型)

ここでは、テーブル名を仮に geodata としておきます。

インポート用構文

まずは構文から見てみましょう。
以下の書き方でインポートできます。

LOAD DATA INFILE '/tmp/geodata.csv' into table geodata FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (address,@latlon1,@latlon2) set latlon=GeomFromText(concat('point(',@latlon1,' ',@latlon2,')'));

簡単な解説を以下に記載しておきます。

LOAD DATA INFILE '/tmp/geodata.csv'
 ┗ インポート用CSVファイル名を指定しています。
into table geodata
 ┗ インポート先テーブル名を指定しています。
FIELDS TERMINATED BY ','
 ┗ フィールドの区切り文字を指定しています(ここではカンマ)。
LINES TERMINATED BY '\r\n'
 ┗ 行の区切り文字を指定しています(ここではCRLF改行)。
IGNORE 1 LINES
 ┗ ラベル行となる1行目を読み込まない、という指定です。
(address,@latlon1,@latlon2)
 ┗ CSVデータ内容に沿って、インポート先カラム名を指定していきます。
   @はユーザ定義変数となるためここでは無視され、この後で使用されます。
set latlon=GeomFromText(concat('point(',@latlon1,' ',@latlon2,')'));
 ┗ 上記定義された変数を結合し、geometry型に変換したものを
   latlonカラムへ格納する、という指定です。

このような感じでユーザ定義変数を使用すれば、異なるデータ形式同士でも、自由自在にインポートすることができます。

とりあえず全部変数に入れて書きたいという場合は、下記のようにカンマ区切りで全てセットしてあげましょう。

LOAD DATA INFILE 【中略】 (@address,@latlon1,@latlon2) set address=@address,latlon=GeomFromText(concat('point(',@latlon1,' ',@latlon2,')'));

なお、インポート時に万が一下記のようなエラーが出てしまった場合は、ファイルの読み込み権限関係の問題となりますので、mysqlユーザでも読み込める領域に移動させるか、「load data infile」を「load data local infile」にしてあげれば通るかと思います。

ERROR 13 (HY000): Can't get stat of '指定ファイルパス' (Errcode: 2)

この記事がなにかしらのお役に立てればなによりです。

 
    このエントリーをはてなブックマークに追加  

  • コメントを残す