2021年8月7日土曜日

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

概要

コマンドラインツールを使ってSQLiteのデータベースにCSVファイルのデータをインポート/エクスポートする方法。
自作のスクリプトでテキストを読込/出力したりDBに接続してinsert/selectをやるよりはるかに簡単。

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

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

環境
  • Windows 10 64bit
  • SQLite3 (3.36.0) Command-Line Shell

CSVファイルを取り込む

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

  • CSVファイルは上記RFCに従って記述しておくのがベスト
  • インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。
既にテーブルがある
  • CSVの一行目はデータとして登録される。
    一行目が不要なら後述する「--skip」オプションを使うこと。
  • テーブルのカラム数とデータ型にCSVを合わせる必要がある。 合っていない場合、エラーになる。
テーブルがない
  • 指定したテーブル名で新しくテーブルが作られる。
  • CSVの一行目がカラム名になり、二行目以降がデータとして挿入される。
  • カラムのデータ型は全てTEXT型になる。
実行例
  1. sample.csvファイルをsample_tableとして取り込む。
    「--csv」オプションでCSVのフォーマットを認識してくれる。
  2. 「--skip 1」オプションでsample.csvファイルの1行目を無視する。
    ファイルにヘッダが含まれていて取り込まれたくない場合に使用する。
  3. 「-v」オプションで取り込みの詳細が出力される。
    下記の場合、CSVファイルからの入力が5行で、 新規挿入された行が4行、エラーは0件 と表示されている。 

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

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

sqlite> -- # 3.
sqlite> .import sample.csv sample_table --csv --skip 1 -v
Added 4 rows with 0 errors using 5 lines of input
「.import」コマンドのオプションが使えるのはver 3.32.0から

SQLite ver 3.32.0未満の環境でCSVデータを取り込む場合は、以下参照のこと。

CSVファイルを書き出す

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

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

sqlite> -- # 1. 
sqlite> .mode CSV

sqlite> -- # 2.
sqlite> .headers on

sqlite> -- # 3. 
sqlite> .once result.csv

sqlite> -- # 4.
sqlite> select * from sample_table;

参考URL

Posted by at
Labels: , , , , ,