CSV を SQL に変換

これは何よりも先に言っておく価値があります。この変換を検索する人の多くにとって、答えは「それは要らない」だからです。Postgres には COPY、MySQL には LOAD DATA INFILE、SQLite には .import、SQL Server には BULK INSERT があり、どれも区切り文字付きファイルを直接読み、列の型を一度だけ適用し、まとめて書き込みます。数千行を超えるものでは、この経路のほうが INSERT 文のスクリプトよりはるかに優れています。

この変換が正しい選択になるのは、好みではなく制約があるときです。バルクローダーはファイルがサーバーから見える場所にある必要があり、しばしばアカウントが持っていない権限も必要とします。手元にあるのがクエリウィンドウだけなら——管理されたデータベースのコンソール、移行ツール、他の人が実行するサポートチケットなど——文しか通貨がなく、400 行分を手作業で生成するのは午後の時間の無駄遣いです。

出力はデータ行 1 つにつき 1 つの INSERT 文のファイルで、それぞれテーブル名と見出し行から取った列名を記します。それより前には何もありません。これは省略ではなく拒否です。CSV は型も長さもキーも NULL 許容かどうかも記録していないので、そこから生成されたスキーマは、DDL 文の権威を装った推測の集まりにすぎません。

推測が外れたときの代償は非対称です。悪い行 1 つなら、悪いレコード 1 つで、DELETE で直せます。サンプルの中でいちばん長い値から推測された VARCHAR(50) は、誰も気づかないまま 2 年間住所を切り詰め続けます。ここでの前提は、テーブルはあなたが設計済みで、自動化したいのは面倒な部分だけだ、というものです。これはこのファイルを求めるほぼ全員に当てはまります。テーブルも必要なら、CREATE 文は自分で書いて先頭に貼り付けてください。生成された文はそのままそこへ適用できます。

CSV の名前から拡張子を除いたものがテーブル名として使われます。その過程で無害化されます。文字・数字・アンダースコア以外はすべてアンダースコアになり、数字で始まる名前には先頭にアンダースコアが付きます。識別子は数字で始められないからです。「2024 orders.csv」というファイルは _2024_orders への INSERT を生成します。

同じ無害化が見出し行から取った列名にも適用され、こちらのほうが意外に感じられやすい部分です。「Order total (£)」という見出しは Order_total____ になり、テーブルの何とも一致しません。元データの見出し行を目的の列名と正確に一致させ、ファイル名を目的のテーブル名にリネームしておけば、生成されたファイルは一切編集不要になります。これには 2 分かける価値があります。代わりの手段は 5 万行にわたる検索置換だからです。

これが CSV の変換と表計算ファイルの変換との違いで、もっとも間違いが起きやすい点です。表計算ソフトのセルは自分が文字列か数値かを知っています。CSV のフィールドは文字の並びなので、パーサーが推測し、その推測結果がそのまま SQL ライターに渡されます。数値として読まれた値は引用符なしで書かれ、文字列のまま残った値は引用符付きで書かれます。

実務上の帰結として、識別子の列は引用符と桁埋めを同時に失います。郵便番号の 01234 は引用符なしの 1234 になり、スペースなしの電話番号は大きな整数になり、1e5 のような型番は 100000 になります。VARCHAR の列に挿入されると、これらはエラーなく桁埋めされていない文字列として収まってしまいます。何も文句を言わないという点で、これは最悪の結果です。識別子の列は引用符付きか非数値の接頭辞付きで書き出すか、実行する前に生成されたファイルを確認してください。最初の文を読むのに 10 秒あれば、テーブル名、列の一覧、各値の種類の例が一度に見られます。

2 つの区切り文字の間の空欄は、空文字列ではなくキーワード NULL になります。これはたいていの場合、書き出しの中の欠落を正しく解釈したものです。NOT NULL 制約が黙って受け入れるのではなく大きな声で拒否してくれる読み方でもあり、それが望ましい振る舞いです。

空欄が本当に空文字列を意味していた場合、そのファイルはもともとその区別を持っておらず、どんな変換ツールもそれを復元できません。テーブルに "" と NULL が違う意味を持つ列がある場合——任意の自由記述欄でよくあります——変換前にその違いを CSV の中で表現しておくか、ステージング段階で COALESCE を追加してください。逆の場合も同様で、すべての空欄を既定値にすべき列は、NULL を既定値の上から挿入するより、INSERT からその列を省くほうがうまく扱えます。

数値と真偽値は引用符なしで書かれます。文字列はシングルクォートで囲まれ、その中のアポストロフィは二重にされます。O'Brien は 'O''Brien' になります。これは ANSI の慣例で、主要なエンジンはどれも同じように読みます。

バックスラッシュによるエスケープはあえて使いません。これは MySQL の拡張機能で、その挙動は文そのものではなく NO_BACKSLASH_ESCAPES というサーバー設定に依存します。つまりそれを使うファイルは、同じバージョンのサーバーでも片方では正しく動き、もう片方ではデータを壊すことがあります。引用符を二重にする方式はどこでも動き、できあがったファイルはどの環境についての前提も持たないままバージョン管理に登録し、複数の環境に対して再実行できます。

CSV の中で true や false と書かれたフィールドは真偽値として読まれ、引用符なしのリテラル TRUE や FALSE として書かれます。Postgres はそれを直接受け入れ、MySQL はその語を 1 と 0 の別名として扱い、SQLite も長年受け入れています。

SQL Server は違います。そのビット型は 1 と 0 を求めており、TRUE を含む文は構文解析に失敗します。運が良ければ最初の文で、運が悪ければ真偽値の列の先頭に空欄が続く 400 番目の文で気づくことになります。行き先が SQL Server なら、実行する前に置換しておいてください。その前に、文字列の列に偶然「on」という単語だけが入っていないかも確認してください。

この変換は日付を解釈しません。2024-03-11 と書かれたフィールドは数値ではないので文字列のまま残り、引用符付きで書かれます。11/03/2024 と書かれたフィールドも同様です。これは見た目より優れています。引用符付きの ISO 形式の日付は、ここで扱うどのエンジンでも日付型の列に正しく挿入され、整った CSV がもっとも読み込みやすいソースになるからです。

同時に、あいまいさもそのまま運ばれます。11/03/2024 は、書き出した人によって 3 月 11 日にも 11 月 3 日にもなり、ファイルはどちらとも語りません。データベースは自分自身のロケール設定に従って解釈し、どちらを選んだかを警告してくれません。CSV の日付形式を確認できないシステムから来たものなら、いったんテキストのステージング列に読み込み、想定を継承するのではなく明示的に変換してください。

文は個別に出力され、BEGIN も COMMIT も複数行の VALUES によるまとめもありません。そのままリモートサーバーで実行すると、5 万回の往復と 5 万回の暗黙のコミットになり、気づくほど遅いうえ、途中で失敗すると半分だけ読み込まれたテーブルが残ります。

2 行加えれば両方解決します。ファイルを BEGIN と COMMIT で包めば、読み込み全体が原子的になり、かなり速くなります。ファイルは中身のデータより大きくもなります。列の一覧が行ごとに繰り返されるからです。列名が長く幅の広いテーブルでは、元の CSV の何倍もの容量の SQL ファイルができることがあり、どこかへメールで送る前に知っておく価値があります。

ファイルの解析と文の書き出しはどちらもこのページで動く素の JavaScript なので、何もアップロードされず待ち行列もありません。作業を制限するのは無料枠の 1 ファイルあたり 100 MB と、そのあとは自分の機械のメモリーです。このペアではこれはとくに重要です。データベースへ向かう CSV はたいてい顧客記録や取引、修正リストであり、それを Web サイトに貼り付けて処理させることこそ、多くのデータ方針が禁じようとしていることだからです。

上限はテーブル全体を一度に保持することなので、数十メガバイトなら余裕をもって収まります。それを超える場合、答えはより大きな変換ツールではなく、最初の節で触れたバルクローダーです。そちらのほうがどのみち優れた答えです。