XLSX を SQL に変換

出力は INSERT 文のファイルで、シートの各行につき 1 つ、それぞれテーブル名と見出し行から取った列名を記します。先頭に CREATE TABLE はなく、これは意図的です。表計算ファイルには型も長さもキーも制約もなく、そこから導き出されたスキーマは、事実を装った創作にすぎません。

1 行の推測ミスは 1 つの悪いレコードで済みます。スキーマの推測ミスは、2 年間住所を切り詰め続ける VARCHAR(50) になります。ここでの前提は、テーブルはすでに設計済みだということで、これはこの変換を必要とするほぼ全員に当てはまります。求められているのは面倒な部分を正しく書き出すことです。テーブルも必要なら、CREATE 文は自分で書いてファイルの先頭に置いてください。2 分あればできますし、誰かが意図的に下すべき判断の記録にもなり、その下の生成された文はそのまま実行できます。

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

実務的な助言はここから直接導かれます。読み込み先のテーブル名にファイルを正確にリネームしてから変換すれば、出力はそのまま実行できます。逆の順番で行うと、5 万行にまたがる検索置換が必要になり、それは機能はしますが、誰かが間違った文字列を置換してしまうもう 1 つの機会でもあります。同じ無害化が見出し行から取った列名にも適用されるので、「Order total (£)」という見出しは Order_total____ になり、あなたの列とは一致しません。変換前に表計算ファイルの見出し行をテーブルに合わせて整えておけば、ファイルはまったく編集不要になります。

数値は引用符なしで書かれます。文字列はシングルクォートで囲まれ、その中のアポストロフィは二重にされます。これは主要なエンジンがどれも理解する ANSI 形式です。O'Brien は 'O''Brien' になります。バックスラッシュによるエスケープはあえて使いません。これは MySQL の拡張機能で、その挙動が文そのものではなくサーバー設定に依存するからです。

空のセルは空文字列ではなくキーワード NULL になります。これは表計算ファイルの空欄にとってほぼ常に正しい解釈で、列が NOT NULL の場合には知っておく価値があります。「なし」と「記録されていない」の違いが本当に重要な場合、表計算ファイルはそもそもその区別を持っておらず、どんな変換ツールもそれを復元できません。値そのものは途中で書式を変えられません。数値はワークブックが保存しているとおりに現れるので、19.99 と小数点 2 桁と通貨記号付きで表示される価格は 19.99 として書かれ、20 と表示されていても実際には 19.995 である値はそのフルの精度で書かれます。

真偽値のセルはリテラルの TRUE か FALSE として書かれます。Postgres はそれを直接受け入れ、MySQL はそれらを 1 と 0 の別名として扱い、SQLite も長年受け入れています。SQL Server は違います。そのビット型は 1 と 0 を求めており、TRUE を含む文は構文解析に失敗します。

これを知っていれば直すのに 2 秒しかかかりませんが、400 番目の文でエラーから気づくと苛立たしい 20 分になります。行き先が SQL Server なら、スクリプトを実行する前にリテラルを置換し、文字列の列にたまたまその単語が含まれていないか確認してください。どの行き先であっても、安全な習慣は、ファイルの最後の文を実行する前に最初の文を見ておくことです。テーブル名、列の一覧、各値の種類の例が 1 行で見え、このファイルに対して行える最速の確認になります。

表計算ファイルは日付を、表示形式をまとった日数として保持しており、この変換は見た目ではなく値のほうを書き出します。2024 年 1 月 1 日は、1899 年 12 月末からの日数として数えられ 45292 になり、タイムスタンプはその数値に小数部が付いたものになります。

数値の列に挿入されると、静かに収まってしまい、しかも間違っています。日付の列に挿入されると拒否されます。こちらのほうがまだましです。どちらも望ましくありません。表計算ファイルの中で列を曖昧さのない形式のテキストに変換して直すか、ステージング用の列に読み込んでからデータベースの中で変換してください。そこではエポックの計算は 1 つの式で済みます。読み込みを繰り返すなら、ステージングのほうが優れた答えです。本物のテーブルに届く前に検証できる場所ができ、日数を日付に変換する処理が、前回誰かが手作業で覚えていた作業ではなく、文書化された手順の一部になります。

シートの中でテキストとして保存されたコードは、引用符付きの文字列として書かれるので、007 は '007' として届き、ゼロ埋めされた口座参照番号も VARCHAR の列に無傷で入ります。これは、同じデータを CSV に通し、不注意な表計算ソフトの往復を経た場合の逆です。そちらでは、変換が始まる前にそれらの列が数値になり、桁埋めはすでに失われています。

ある列が識別子かどうかを判断する有効な試験は、その 2 つの値を足し算することに意味があるかどうかです。郵便番号、品番、電話番号、参照コードはどれもこの試験に落ち、シートの中でも読み込み先のテーブルでもテキスト列として扱われるべきです。注意すべきなのは逆のケースです。ワークブックがどこかの段階でコードを数値として保存していた場合、桁埋めはすでに失われており、文は 7 という値を VARCHAR の列へ、何の問題もなく、しかしまったく間違って挿入します。

すべての文は、テーブルの列順に頼るのではなく列を明示的に名指しします。これにより、あとから列が増えたテーブルに対してもファイルが機能します。列の集合はシート全体で見つかったフィールドの和集合なので、あとのほうの行にしか現れないフィールドも一覧に含まれ、それを持たない行には NULL が補われます。

この冗長さには知っておく価値のあるコストがあります。列の一覧が行ごとに繰り返されるので、ファイルは中身のデータより大きくなります。6 列で 5 万行のデータは、元になった .xlsx のおよそ 4.3 MB に対して、およそ 5.7 MB の SQL になりました。これはここで扱う変換先の中で唯一、ワークブックより大きくなるものです。

文は 1 行につき 1 つ、トランザクションで包まれることも複数行の VALUES によるまとめもなく出力されます。そのまま Postgres や MySQL に対して実行すると、5 万回の往復と 5 万回の暗黙のコミットになり、気づくほど遅く、途中で失敗すると半分だけ読み込まれたテーブルが残ります。

ファイルを BEGIN と COMMIT で包めば、両方が 2 行で解決します。本当に大きな読み込みには、バルク方式のほうが優れています。Postgres の COPY、MySQL の LOAD DATA、SQLite の .import は、区切り文字付きファイルを直接読み、桁違いに速くなります。その経路には CSV か TSV への変換を使ってください。

変換されるのはワークブックの最初のシートです。一連の INSERT 文は 1 つのテーブルを対象にするものだからです。目的のシートが最初でないなら、移動してから変換し直してください。複数のシートを複数のテーブルに読み込むなら、対象のテーブルごとにファイル名を変えてシートごとに変換してください。

すべてローカルで行われます。ワークブックはページの中で解析され、文が書き出され、アップロードもファイルを運ぶリクエストもありません。データベースへ向かう表計算ファイルはたいてい顧客記録や取引、その他 Web サイトに貼り付けて処理させるべきでないものなので、このペアではこれがとくに重要です。

目的のテーブルが空で件数が多いなら、SQL を生成するのは遠回りです。どのデータベースも区切り文字付きファイルを直接読み、型を一度だけ適用し、まとめて書き込むバルクローダーを持っており、数千行を超えるものでは INSERT 文のスクリプトを楽々上回ります。

INSERT 文が意味を持つのは、読み込みが小規模なとき、実行前にレビューされたりバージョン管理に登録されたりする必要があるとき、複数の環境に対して再実行されるとき、あるいは手元にあるのがファイルシステムではなくクエリウィンドウだけのときです。これは実在するよくある状況で、この変換はまさにそのためにあります。