2019年9月13日金曜日

SQLite3 CSVデータを取り込む、CSVデータを出力する(～ver 3.31.1)

概要

テキストデータであるCSVファイルをSQLite3に入力/出力させる方法。
コマンドラインツールから利用する。
自作のスクリプトでテキストを読込/出力したりDBに接続してinsert/selectをやるよりはるかに簡単。
取り込む処理をインポート/バルクロード、出力する処理をエクスポートと言ったりする。

Update!

SQLiteのver 3.32.0 からは「.import」コマンドにオプションが追加され、CSVファイルが取り込みやすくなった。 (「.mode」コマンドでCSVモードに変更しなくてもいい)

CSVのフォーマットについて

CSVはRFCでフォーマットが定義されている。
RFCにはダブルクォートの取り扱いや改行コードなどについて記載されているので、一度は目を通しておくべき。
アプリケーションによって、対応していなかったり拡張されていたりするのでややこしい。

環境
  • Windows 10 64bit
  • SQLite3 ver.3.29.0

CSVファイルを取り込む

CSVファイルをインポートするに当たっては以下のことに注意する。

  • CSVファイルは上記RFCに従って記述しておくのがベスト
  • インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。
既にテーブルがある
  • CSVの一行目はデータとして登録される。
    一行目が不要ならあらかじめ削除しておくこと。
  • テーブルのカラム数とデータ型にCSVを合わせる必要がある。
    合っていない場合、エラーになる。
テーブルがない
  • 指定したテーブル名で新しくテーブルが作られる。
  • CSVの一行目がカラム名になり、二行目以降がデータとして挿入される。
  • カラムのデータ型は全てTEXT型になる。
実行例
  1. CSVモードに切り替え
  2. sample.csv ファイルを sample_tableに挿入

sqlite> -- # 1.
sqlite> .mode csv
sqlite> -- # 2.
sqlite> .import sample.csv sample_table

CSVファイルを書き出す

手順は以下の実行例を参照のこと。 SQLiteコマンドラインツールのCSVをエクスポート機能には、以下の特徴がある。

  • RFCに従ってエスケープ処理をやってくれる。
  • 出力先に同名のファイルが存在していても、上書き確認はしてくれない。
  • 「.mode csv」 を利用し「.separator ,」は利用しないこと。
    「.separator ,」ではカンマやダブルクォーテーションがエスケープ処理されない。
実行例
  1. CSVモードに切り替え
  2. 次に打ち込むコマンドの結果をresult.csvに出力
  3. 出力したいコマンドを実行する

sqlite> -- # 1. 
sqlite> .mode CSV
sqlite> -- # 2. 
sqlite> .once result.csv
sqlite> -- # 3.
sqlite> select * from sample_table;

参考URL

Posted by at
Labels: , ,