Excel ファイルを MySQL データベースにインポートする

Excel ブックのデータを MySQL に取り込むには、ワークシートを CSV として保存して LOAD DATA または phpMyAdmin で読み込む方法と、Sqlizer.io でブックを SQL に変換する方法があります。CSV を使う方法では、取り込み先に互換性のあるテーブルが必要です。Sqlizer.io はブックから変換できますが、実行前に生成された SQL を確認してください。以下の手順では、id、firstname、lastname、gender、city の各列を持つ学生テーブルを例にします。

LOAD DATA ステートメントでデータをインポートする

ワークシートを CSV（カンマ区切り）として保存します。列の順序と意味が取り込み先テーブルと一致していることを確認してください。この例では、CSV にヘッダー行があり、各項目がカンマで区切られ、文字列が二重引用符で囲まれ、Unix 形式の改行コードが使われているものとします。また、初回の取り込み時点では取り込み先テーブルが空であることを前提とします。再度インポートすると行が重複することがあるため、再実行する場合は既存の行を削除するか、重複を処理する設定を行ってください。実際に保存したファイルに合わせて形式のオプションを調整します。

読み込み前に、Python の標準ライブラリを使って CSV の代表的な行を確認できます。次の小さな例では、サンプルテーブルと同じ列名を持つ 1 行を使います。

import csv
from io import StringIO

sample = StringIO("id,firstname,lastname,gender,city\n1,Ada,Lovelace,F,London\n")
row = next(csv.DictReader(sample))
print(f"{row['firstname']} {row['lastname']} — {row['city']}")

Ada Lovelace — London

この確認では、ヘッダー名と値が意図どおりに読み取られるかを調べます。MySQL への接続や、後述のインポート文の実行は行いません。

MySQL 8.0.27 の例では、Windows のサーバー側アップロードディレクトリを使用しています。LOAD DATA INFILE は MySQL サーバーのファイルシステム上にあるパスを読み込むため、そのファイルにサーバーからアクセスでき、secure_file_priv の設定で許可されている必要があります。MySQL Workbench では、対象サーバーに接続したクエリタブで文を実行します。Workbench を使っても、手元のコンピューター上のパスがサーバーから見えるようになるわけではありません。コマンドラインクライアントでも、同じサーバー側のパス規則が適用されます。LOAD DATA LOCAL INFILE を使うと、クライアントがファイルを読み取って送信します。この場合は、クライアントとサーバーの両方でローカル読み込みが許可されている必要があります。アクセスを拒否された場合は、MySQL の LOAD DATA に関するセキュリティガイダンスを確認してください。

サンプルファイルには、LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/students.csv' INTO TABLE tb_students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; を使用します。環境に合わせてパス、テーブル名、区切り文字、囲み文字、改行コードを置き換えてください。タブ区切りのデータには FIELDS TERMINATED BY '\t'、半角スペース区切りのデータには FIELDS TERMINATED BY ' ' を指定します。Windows の CSV ファイルでは \r\n の改行コードがよく使われます。行が想定どおりに分割されない場合は、ファイルの実際の改行コードを指定してください。IGNORE 1 ROWS はヘッダーを読み飛ばす指定であり、列名に基づいて列を対応付けるものではありません。ファイルに id がない場合や列の順序が異なる場合は、テーブル名の後に対応する取り込み先列を指定し、自動生成される値が意図どおりに扱われることを確認してください。

読み込み後、Workbench でテーブルを確認するか、SELECT id, firstname, lastname, gender, city FROM tb_students LIMIT 10; を実行します。表示された行を CSV と照合し、データを使う前に取り込まれた行数も確認してください。

方法 2：Sqlizer.io でデータをインポートする

ワークシートを先に CSV として保存せずにブックを変換したい場合は、Sqlizer.io を使います。ブックと変換先のデータベースの種類として MySQL を選び、1 行目にヘッダーが含まれているかを指定します。アクティブなワークシートを選ぶか、students のようにワークシート名を入力し、変換する範囲としてシート全体またはセル範囲を選択します。取り込み先のテーブル名を設定して SQL を生成します。

生成された文を実行する前に、テーブル定義、列名、値、テーブル作成や存在確認の処理が含まれているかを確認してください。意図したデータベースに SQL を実行したら、テーブルを調べ、代表的な行をブックと照合します。ブックには機密データが含まれることがあります。外部の変換サービスにアップロードする前に、データの機密性とサービスの利用規約を確認してください。

方法 3：phpMyAdmin でデータをインポートする

この方法でも CSV ファイルを使います。ワークシートを CSV として保存し、対象のデータベースを開いて、CSV の列に合うテーブルを選択します。例として使う tb_students_phpmyadmin テーブルには、id、firstname、lastname、gender、city の各列があります。phpMyAdmin でテーブルの インポート タブを開き、CSV ファイルを選択して、形式として CSV を指定します。項目の区切り文字、文字列の囲み文字、改行コード、ヘッダー行を読み飛ばすかどうかなど、ファイルに合う形式オプションを設定します。実行 をクリックしてインポートを開始します。

CSV の列がテーブルと一致しない場合は、インポート前にテーブルまたはファイルを修正してください。形式として CSV を選んでも、ヘッダー名に基づいて列が対応付けられるわけではありません。インポートが完了したら、テーブルを表示するか行を取得するクエリを実行し、いくつかの値と行数を元のファイルと照合してください。

まとめ

ワークシートを CSV として保存でき、MySQL サーバーがファイルにアクセスできる場合は LOAD DATA を選びます。ブラウザー上で CSV を取り込む場合は phpMyAdmin を使います。Sqlizer.io はブックを SQL に変換するため、内容を確認してから目的のデータベースで実行できます。どの方法でも、元データの列と形式を取り込み先テーブルに合わせ、挿入された行を確認してください。