SqliteでCSVインポート

sqliteでCSVファイルをインポートする方法と注意点です。

インポートしたいのは以下の reservation テーブル。

sqlite> .schema reservation
CREATE TABLE RESERVATION (
    ID integer primary key autoincrement, -- autoincrementなしはdelete後再利用される。問題ないとは思うが念のため付与する。
    STATUS char(2),  -- AC:受付中, OK:確定, NG:先約ありNG, CA:キャンセル
    -- フォーム
    CHECKIN_DATE date, -- localtime で格納
    CHECKIN_TIME integer, -- 時のみ
    CHECKOUT_DATE date, -- localtime で格納
    NUM_DORMITORY integer, -- ドミトリー人数
    NUM_WOMAN_ONLY_DORMITORY integer, -- 女性専用ドミトリー人数
    NUM_PRIVATE_ROOM_A integer, -- 個室A人数
    NUM_PRIVATE_ROOM_B integer, -- 個室B人数
    NAME varchar(256), -- 名前(漢字)
    NAME_KANA varchar(256), -- 名前(かな)
    TEL varchar(20), -- 電話番号
    MAIL_ADDRESS varchar(256), -- メールアドレス
    USE_PARKING boolean, -- 駐車場使用有無
    MESSAGE varchar(1024), -- 連絡事項
    --
    MEMO varchar(1024), -- メモ
    CREATED_AT timestamp default (datetime('now','localtime')),
    MODIFIED_AT timestamp default (datetime('now','localtime'))
    );

評価用のデータを作成しようと、「テストデータ・ジェネレータ」というありがたいツールでデータを作成し、Excelにコピペして、ほかのデータも適当に作ってCSVファイルに保存しました。

sqlite でCSVをインポートする方法はこんなかんじ。

$ sqlite3 hogedb.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .separator ,
sqlite> .import /tmp/reservation.csv reservation
sqlite> 

でもそう簡単にはいかなくて、こんなエラーが出まくります。

/tmp/reservation.csv:1: expected 18 columns but found 11 - filling the rest with NULL
/tmp/reservation.csv:1: INSERT failed: datatype mismatch
/tmp/reservation.csv:2: expected 18 columns but found 11 - filling the rest with NULL
/tmp/reservation.csv:2: INSERT failed: datatype mismatch
/tmp/reservation.csv:3: expected 18 columns but found 11 - filling the rest with NULL
/tmp/reservation.csv:3: INSERT failed: datatype mismatch
              ：

「expected 18 columns but found 11」のほうはほっといてもいいんですが、行末に不足分の「,」を追加すると出なくなりました。

「INSERT failed: datatype mismatch」のほうがやっかいで、文字列をダブルクォートで囲んだりしてみましたが、どの列のデータが悪いのかもわからないため四苦八苦しました。

最終的に、1行だけのCSVファイルを作成し、後ろから１列ずつ消していったのですが、最後に残ったIDが空だったのが悪かったようです。だめだったのがこれ↓。「OK」の前、最初の列(ID)が空だったのが原因。

,OK,"2021-07-11",19,"2021-07-13",1,0,0,0,"笠原 久江","カサハラ ヒサエ","090-3474-7472","kasahara1219@tokyo24.com",1,,,,

IDはPKだから insert するときは自動生成してくれます。.import でインポートするときも自動生成してくれるものと思い込んでいたのですが、してくれませんでした。

CSVの編集はEmacsの replace-regexp でダブルクオートで囲ったり、行末にコンマを追加したりしましたが、IDを連番で入れるには、M-:(eval-expression)で以下の式を評価して実行しました。（M-< で先頭行に行ってから。）

(let ((l 1)) (while (not (eobp)) (beginning-of-line)(insert (format "%d" l))(setq l (1+ l))(forward-line 1)))

date型も注意が必要です。ダブルクォートなしの「2021-07-11」でインポートしたら「2003」が入りました。そりゃそうだ。