SQLiteでCSVファイルのインポート

郵便番号cvsを.importする

実践的な運用を想定してデータの大量投入をやってみます。こういうときはやっぱり、日本郵政公社の郵便番号データがもってこいです。

テーブルを作成するsql

$ cat create-tbl.sql
create table zip (
        col1 text not null,
        col2 text not null,
        col3 text not null,
        col4 text not null,
        col5 text not null,
        col6 text not null,
        col7 text not null,
        col8 text not null,
        col9 text not null,
        col10 integer not null,
        col11 integer not null,
        col12 integer not null,
        col13 integer not null,
        col14 integer not null,
        col15 integer not null
);

よく使いそうなところにindexを作るsql

$ cat create-idx.sql
create index old_zip_idx on zip(col2);
create index new_zip_idx on zip(col3);
create index kanji_todofuken_idx on zip(col7);
create index kanji_shikuchoson_idx on zip(col8);
create index kanji_chouiki_idx on zip(col9);

インポートするまでのシェルスクリプト

#!/bin/sh
# 元データを取ってくる
wget -q "http://www.post.japanpost.jp/zipcode/dl/kogaki/lzh/ken_all.lzh"
 
# 解凍
lha e ken_all.lzh
 
# SJIS から UTF-8 に変換, <CR>の削除
# csvの形式が "...","...", ... と文字列を'"'で囲っているので、'"'を削除
iconv -f SJIS -t UTF-8 < ken_all.csv | tr -d "\r" | tr -d '"' > new.csv
 
# tableの作成
sqlite3 zip.db < create-tbl.sql
 
# 区切り文字を','にしてcsvファイルをzipテーブルにインポート
sqlite3 -separator , zip.db ".import new.csv zip"
 
# indexの作成
sqlite3 zip.db < create-idx.sql
 
rm -f ken_all.csv new.csv ken_all.lzh

このシェルスクリプトを実行すると、DBが完成します。

データがちゃんと入ったか確認

$ sqlite3 zip.db "select * from zip where col3='0600000';"
01101|060  |0600000|ホッカイドウ|サッポロシチュウオウク|イカニケイサイガナイバアイ|北海道|札幌市中央区|以下に掲載がない場合|0|0|0|0|0|0

うまくいきましたね。