CSVを違うデータ形式のテーブルへコマンド一つでインポート(MySQL)
CSVファイルをデータベースにインポートする際、簡単なプログラムを作ってインポートされる方も少なくはないかと思いますが、私は半々くらいです。
プログラムを作らない場合は、コマンド一つでインポートできるLOAD DATA INFILE構文を使用しますが、最近結構イレギュラーなデータを入れるようになってから、何度もこのオプションを見直すようになってしまったので、一度ここに書き残しておきたいと思います。
CSVの2つの項目を結合したものを1つのカラムへインポート
今回の目的です。
load data infileでぐぐればいくらでも基本的な例は出てきますので、ここではユーザ定義変数を使用し、CSVファイルとテーブルのカラム数がイコールでなかった場合のインポート例を紹介したいと思います。
さらにそれに加え、ユーザ定義関数を使用するのに意味がありそうな、geometry型のカラムに位置情報を格納する構文も入れておきます。
バラバラに書いてしまったのでわかりにくいかと思いますが、つまりは何がしたいのかをまとめると、以下となります。
以下のデータが格納されている。
・住所 (文字列)
・緯度 (XX.XXXXXX)
・経度 (XXX.XXXXXX)
ここでは、CSVファイルパスを仮に /tmp/geodata.csv としておきます。
[テーブル]
以下のカラムが用意されているので、上記緯度経度をlatlonカラムへ格納する。
・address (varchar型)
・latlon (geometry型)
ここでは、テーブル名を仮に geodata としておきます。
インポート用構文
まずは構文から見てみましょう。
以下の書き方でインポートできます。
簡単な解説を以下に記載しておきます。
┗ インポート用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カラムへ格納する、という指定です。
このような感じでユーザ定義変数を使用すれば、異なるデータ形式同士でも、自由自在にインポートすることができます。
とりあえず全部変数に入れて書きたいという場合は、下記のようにカンマ区切りで全てセットしてあげましょう。
なお、インポート時に万が一下記のようなエラーが出てしまった場合は、ファイルの読み込み権限関係の問題となりますので、mysqlユーザでも読み込める領域に移動させるか、「load data infile」を「load data local infile」にしてあげれば通るかと思います。
この記事がなにかしらのお役に立てればなによりです。
