【TermuxとsqliteとVim】全国の郵便番号CSVをsqliteにインポートする(メモ

SQLの練習がてら、全国郵便番号のCSVファイルをsqliteにインポートする際に手こずったのでメモ。

※ Termuxとvimを触ったことがある方対象記事です。

実行環境

• Androidスマホ
• Termuxアプリ F-Droid版
• sqlite
• Vim

• Termuxへのsqliteのインストールは

$ pkg install sqlite

• sqliteの対話モードの起動方法

sqliteのデータファイル指定の場合。ファイルが無ければ新規作成されます(テーブル作成後に)

$ sqlite3 データファイル名

プロンプトがsqlite >に変わります。あとはSQLを打つのみ。

今回おこった問題と対処

何をしようとしていたかですが、

1. 郵便局のサイトからDLした全国郵便番号のCSVを、
2. AndroidのTermuxアプリにインストールしたsqliteの対話式シェルでインポートしようとしたら、
3. 異常に時間が掛かる上に、途中でエラーを起こしてストップした。
4. そこで、vimでCSVを開いたら文字化けしていた。
5. 従って、vimでCSVをutf-8に変換し上書き保存し直して、
6. sqliteの対話式シェルからコマンドでCSVをインポートした。
7. めでたし。

郵便番号のCSV(全国一括 ken_all.zip)入手

こちらでDLしました

郵便番号データダウンロード - 日本郵便

• zipファイルなのでLinuxコマンドで解凍します。

$ unzip ken_all.zip

カレントディレクトリにKEN_ALL.CSVで解凍されます。

sqliteにCSVをインポートします

データベースファイル名「sample.db」(新規作成)、テーブル名「Code」(新規作成)

失敗編

# データベースファイルに接続
$ sqlite3 sample.db

# sqliteが起動しプロンプトが変わる

/* csvモードに変更 */
sqlite > .mode csv

/* ファイル名とテーブル名を指定してインポート */
sqlite > .import KEN_ALL.CSV Code

/* ズラズラっとインポートしている風な文字が流れ、やがて止まる。*/

/* テーブル確認 */
sqlite > .tables
sqlite >

テーブルが作られていない。

VimでCSVをutf-8に変換、保存作業

参考 : VimでShift_JISのファイルをUTF-8に変換 | ハックノート

• vimでCSVを確認のため開く

$ vim KEN_ALL.CSV

文字化けしていた。

• vimでCSVをutf-8に変換し保存し直す。

vimでCSVファイルを開く。

$ vim KEN_ALL.CSV

文字コードの確認。

:set fenc?

/* Shift_JISの場合はfileencoding=latin1 と表示される。された。*/

shift-jisで開き直す。vimは閉じず、そのまま以下のコマンドを入力する。

:e ++enc=shift-jis

これで文字化けの表示が直った。

文字コードをutf-8に変換する。

:set fenc=utf-8

最後に上書き保存する。utf-8で保存される。

:wq

CSVをutf-8で上書き保存完了。

sqliteでCSVのインポート再チャレンジ

shift-jisからutf-8に変換し直したCSVをインポートします。上手くいくかな？

# sqliteファイルに接続
$ sqlite3 sample.db


/* モードをcsvに変更 */
sqlite > .mode csv

/* 対話sqliteでコマンド入力しCSVインポート */
sqlite > .import KEN_ALL.CSV Code
sqlite >

1秒も掛からずプロンプトが表示された。もう完了？

データがインポートされたか確認する。

/* テーブルの存在確認 */
sqlite > .tables
Code

/* 全データ表示 */
sqlite > select * from Code;

/* ズラズラっとデータが表示 */

一番最後の沖縄県の与那国のデータまで表示し終えるのに15秒ほど掛かった。エラーなし、全てインポートされたようです。

sqlite対話モードの終了方法

sqlite > .exit

/* または */

sqlite > .quit

/* または */
sqlite > .q

/* または */
Ctrl + d キー のショートカット

補足

CSVをテーブル無しの状態でインポートすると、先頭行が見出し行になる。型は全てTEXT型。
今回いれた郵便番号のCSVファイルではヘッダー行が入っていなかったので変更が必要になる。

sqliteのDBビューワーアプリで見てみたら、レコードが１つのカラムに入っていた…全然ダメじゃん…

上手く行くやり方

郵便番号 CSV データを SQLite 3 にインポート（SQLite 3 を使用）

こちらのリンクは福山大学の金子研究室ページ。同じテーマの記事がある。

方法として、

1. CSVファイルをutf-8で保存する。
2. sqlite3の対話モードを起動。
3. テーブルを作成。15カラムでそれぞれ型指定する。
4. モードをcsvに変更。
5. utf-8で保存したCSVファイルをテーブルにインポート。

12万行ありますが、データのインポートは一瞬で終わる。
テーブルは先に作っておいた方が良さそうです。たとえばPandasのpd.read_sql()でsqliteを読み込む場合、テーブルを作らずインポートしたものは型が全てTEXT型で入れられる為にobject型になる。いちいちastype(int)みたいな変換をしないといけない。

以上です。