CSVファイルからデータを抽出したい場合、データ量が膨大だったり、条件が複雑だったりした場合、
「あー、SQLで検索かけたいなー。でもわざわざDBにテーブル作ってINSERTするのも面倒だなー」
とか思ったりすることがあります。

そんな願望を叶える小技を見つけたので紹介します。

概要

H2DBというDBがありますが、このJDBCドライバーを通して、例えば下記のようなSQLを実行すると、生CSVファイルに対して検索がかけられます。

select
        *
    from 
        csvread(
            -- CSVファイルパス
            '/Hoge.CSV',
            -- カラム名一覧。カンマ区切り
            'TEST1,TEST2',
            -- 文字コード
            'Shift-JIS'
        )
    where
        TEST1 like 'あいう%'

ドライバーを使う設定だけすればよく、DBをセットアップしたり、テーブルを作成する必要はありません。
LIKE文、正規表現なども使えて便利です。
あとJOINとかも出来るようです。

やること

H2DBドライバーをダウンロード
お好みのDBビューアーをセットアップ
郵便番号一覧がのったCSVファイルをダウンロード
SELECTしてみる
Javaから操作してみる

H2DBドライバーをダウンロード

下記からJDBCドライバーをダウンロードしましょう。

ちなみに、H2DBはこのJarファイル一個で、DBそのものと、ドライバーがセットになっています。
その辺りの話もいつかまとめたいですね。

お好みのDBビューアーをセットアップ

好きなDBビューアーをセットアップしましょう。
ダウンロードしたjarファイルをを登録し、接続情報を追加します。
接続情報はこんなふうになります。

ドライバー名：org.h2.Driver
接続URL：jdbc:h2:mem:hoge（hogeの部分はどんな値でも可）
ユーザー：空でいい
パスワード：空でいい

まあCSVファイルの操作なので、H2DB自体を使うわけではないのですが、一応接続出来るようにする必要があります。
H2DBは、上記のように「jdbc:h2:mem」などの接続URLにするとDBを起動とかしなくても接続できるモードになります。

郵便番号一覧がのったCSVファイルをダウンロード

せっかくなので郵便番号とかのったCSVを検索してみましょう。
ここで公開しているみたいなので、「全国一括」をクリックしてダウンロードして解凍しましょう。

SELECTしてみる

さて、これで設定は終了です。では下記のようなSQLでSELECTしてみましょう。

select
        *
    from 
        csvread(
            -- CSVファイルパス
            '/xxx/KEN_ALL.CSV',
            -- カラム名一覧。カンマ区切り。CSVの一行目にカラム名が記載されている場合は、nullにする。なぜか大文字しかダメな模様。
            'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
            -- 文字コード
            'Shift-JIS'
        )
    where 
        ZIP_CODE = '9071801'

結果

LOCAL_GOVERNMENT_CODE   OLD_ZIP_CODE    ZIP_CODE    KANA1   KANA2   KANA3   ADDRESS1    ADDRESS2    ADDRESS3    PREF_CODE
"47382" "90718" "9071801"   "ｵｷﾅﾜｹﾝ"    "ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ" "ﾖﾅｸﾞﾆ"   "沖縄県" "八重山郡与那国町"  "与那国" "0"

ポイントは、

csvread(
    -- CSVファイルパス
    '/xxx/KEN_ALL.CSV',
    -- カラム名一覧。カンマ区切り。CSVの一行目にカラム名が記載されている場合は、nullにする。なぜか大文字しかダメな模様。
    'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
    -- 文字コード
    'Shift-JIS'
)

の部分です。
まあコメントで記載した通りなんですが。

他にも、こんな風に正規表現が使えたりします。

select
        *
    from 
        csvread(
            '/xxx/KEN_ALL.CSV',
            'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
            'Shift-JIS'
        )
    where 
        ADDRESS3 regexp '（.*階）$'
    limit 2

結果

LOCAL_GOVERNMENT_CODE   OLD_ZIP_CODE    ZIP_CODE    KANA1   KANA2   KANA3   ADDRESS1    ADDRESS2    ADDRESS3    PREF_CODE
"04101" "980  " "9806101"   "ﾐﾔｷﾞｹﾝ"    "ｾﾝﾀﾞｲｼｱｵﾊﾞｸ" "ﾁﾕｳｵｳｱｴﾙ(1ｶｲ)" "宮城県" "仙台市青葉区"    "中央アエル（１階）"   "0"
"04101" "980  " "9806102"   "ﾐﾔｷﾞｹﾝ"    "ｾﾝﾀﾞｲｼｱｵﾊﾞｸ" "ﾁﾕｳｵｳｱｴﾙ(2ｶｲ)" "宮城県" "仙台市青葉区"    "中央アエル（２階）"   "0"

他のCSVと組み合わせてJOINとか、H2DBと連携して、H2DBにデータを入れたり、H2DBの内容をCSVに吐き出したりもできます。

csvread関数のオプションを変えれば、TSVとかからも検索出来るみたいです。

select
        *
    from 
        csvread(
            -- TSVファイルパス
            '/xxx/KEN_ALL.TSV',
            -- カラム名一覧。指定する区切り文字に合わせて、カラム名の区切り文字も変える
            'LOCAL_GOVERNMENT_CODE  OLD_ZIP_CODE    ZIP_CODE    KANA1   KANA2   KANA3   ADDRESS1    ADDRESS2    ADDRESS3    PREF_CODE',
            -- 文字コード
            'Shift-JIS',
            -- 区切り文字。char(9)がタブ文字とのこと
            char(9)
        )
    limit 2;

・・・なんか知らないけど文字化けましたが。
はて。

Javaから操作してみる

JDBCドライバーなので、当然Javaから操作もできます。

下記にコミットしてみました。

参考まで。

ちなみにSQLファイルの実行には"Mirage"というORマッパーを使っています。
http://dev.classmethod.jp/server-side/java/mirage-sql-for-java/
http://amateras.sourceforge.jp/site/mirage/welcome.html

これもとても便利なライブラリなので、いずれまとめたいですねー。

生CSVファイルに対してSELECTして検索する方法

概要