MySQL で CSV ファイルをインポートする (LOAD DATA)

ここでは MySQL で LOAD DATA 文を使って CSV ファイルをインポートする方法をご説明します。

目次
  1. LOAD DATA の Local ファイルインポートを有効化する
  2. CSV ファイルとデータをインポートするテーブルを準備する
  3. LOAD DATA LOCAL INFILE 文で CSV ファイルをインポートする
  4. " " で囲まれた値を持つ CSV ファイルをインポートする

LOAD DATA の Local ファイルインポートを有効化する

MySQL で CSV ファイルなどをインポートするには、後ほどご説明しますが LOAD DATA 文の INFILE を使います。

今回は LOCAL オプションを使って、クライアント側にある CSV ファイルをインポートします。


LOAD DATA の Local ファイルインポートを有効化しないで、LOAD DATA LOCAL INFILE 文を実行すると、次のようなエラーになります。

Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides

Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.


ここでは、LOAD DATA の Local ファイルインポートを有効化する方法をご説明します。

まず、以下のクエリーを実行して、グローバルシステム変数の local_infile を ON にします。

SET GLOBAL local_infile = 1;

SHOW VARIABLES 文で現在の local_infile の設定が確認できます。 

SHOW VARIABLES LIKE 'local_infile';

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 1


次に、MySQL Workbench を一度閉じて再起動し、最初の画面の MySQL Connections のインスタンスで右クリックし [Edit Connection...] を選択します。

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 2


Manage Server Connections の画面が表示されるので、 [Connection] タブの [Advanced] タブを選択します。

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 3


Others: に OPT_LOCAL_INFILE=1 を追加し、Close して、いつも通りに MySQL Server に接続します。

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 4


これで LOAD DATA の Local ファイルインポートが有効化できました。


CSV ファイルとデータをインポートするテーブルを準備する

CSV ファイルとデータをインポートするテーブルを準備します。

以下のデータを students1.csv というファイル名で /Users/user1/Temp/ に保存しました。

student_number,first_name,last_name,birthday,gender
S000101,Mio,Nakata,2019-06-06,F
S000102,Haruto,Nakata,2018-11-17,M
S000103,Ren,Tada,2020-04-09,M
S000104,Mio,Kato,2019-09-27,F

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 5


次に、データを保持するテーブルを作ります。

csv_test_db というデータベースを作成して、students テーブルを作成するクエリーです。

csv_test_db という名前のデータベースが既に存在する場合は削除されてしまうのでご注意ください。

DROP DATABASE IF EXISTS csv_test_db;
CREATE DATABASE IF NOT EXISTS csv_test_db;
USE csv_test_db;

CREATE TABLE students (
    student_id      INT             NOT NULL AUTO_INCREMENT,
    student_number  VARCHAR(10)     NOT NULL,
    first_name      VARCHAR(50)     NOT NULL,
    last_name       VARCHAR(50)     NOT NULL,
    middle_name     VARCHAR(50)     NULL,
	birthday  	    DATE            NOT NULL,
    gender          ENUM ('M','F')  NOT NULL,
    paid_flag       BOOL            NOT NULL DEFAULT FALSE,
    PRIMARY KEY (student_id),
    UNIQUE KEY (student_number),
    CHECK (birthday >= '2000-01-01')
);

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 6

LOAD DATA LOCAL INFILE 文で CSV ファイルをインポートする

MySQL で CSV ファイルをインポートするのに、LOAD DATA 文の INFILE を使います。

今回は LOCAL オプションを使って、クライアント側に保存されている CSV ファイルをインポートします。


students1.csv の CSV ファイルを students テーブルにインポートする LOAD DATA LOCAL INFILE 文は次の通りです。

LOAD DATA LOCAL INFILE '/Users/user1/Temp/students1.csv' 
	INTO TABLE students
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 ROWS
    (student_number, first_name, last_name, birthday, gender);

1 行目 LOAD DATA LOCAL INFILE に続けて CSV ファイルのパスと名前を指定します。 Windows のパスは 'C:\\Temp\\students1.csv' や 'C:/Temp/students1.csv' のような感じで指定しても大丈夫です。

2 行目 INTO TABLE の後にテーブル名を指定します。

3 行目 FIELDS TERMINATED BY ではフィールドの区切り文字を指定します。 今回は CSV ファイルなので ',' を指定しています。

4 行目 LINES TERMINATED BY ではラインの区切り文字を指定します。 Mac なので '\n' を指定しています。 Windows では '\r\n' 指定してください。

5 行目 IGNORE n ROWS は今回ヘッダー行があるので 1 行無視するようにしています。CSV ファイルがデータ行から始まる場合は必要ありません。

6 行目 でデータが順番に students テーブルのどのカラムにあたるかを指定しています。CSV ファイルにテーブル全カラムの値が順番に含まれている場合は指定する必要はありません。


上の LOAD DATA LOCAL INFILE 文を実行して、students テーブルの中身を確認します。

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 7

students1.csv ファイルに入っていたデータが students テーブルにインポートされていますね。


" " で囲まれた値を持つ CSV ファイルをインポートする

次に MySQL で " " で囲まれた値を持つ CSV ファイルをインポートしてみましょう。

csv_test_db データベースに remarks カラムを追加した students2 テーブルを作成します。

CREATE TABLE students2 (
    student_id      INT             NOT NULL AUTO_INCREMENT,
    student_number  VARCHAR(10)     NOT NULL,
    first_name      VARCHAR(50)     NOT NULL,
    last_name       VARCHAR(50)     NOT NULL,
    middle_name     VARCHAR(50)     NULL,
	birthday  	    DATE            NOT NULL,
    gender          ENUM ('M','F')  NOT NULL,
    paid_flag       BOOL            NOT NULL DEFAULT FALSE,
    remarks 		VARCHAR(100) 	NULL,
    PRIMARY KEY (student_id),
    UNIQUE KEY (student_number),
    CHECK (birthday >= '2000-01-01')
);

以下のデータを students2.csv というファイル名で /Users/user1/Temp/ に保存しました。

4 行目のレコードの remarks に "テスト,abc" という値が入っています。

student_number,first_name,last_name,birthday,gender,remarks
S000101,Mio,Nakata,2019-06-06,F,
S000102,Haruto,Nakata,2018-11-17,M,
S000103,Ren,Tada,2020-04-09,M,
S000104,Mio,Kato,2019-09-27,F,"テスト,abc"

この students2.csv の CSV ファイルを students2 テーブルにインポートするクエリーは次の通りです。

追加で 4 行目に ENCLOSED BY で '"' を指定しています。

LOAD DATA LOCAL INFILE '/Users/user1/Temp/students2.csv' 
	INTO TABLE students2
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 ROWS (student_number, first_name, last_name, birthday, gender, remarks);

上の LOAD DATA LOCAL INFILE 文を実行して、students2 テーブルの中身を確認します。

MySQL で CSV ファイルを LOAD DATA を使ってインポートする方法 8

students2.csv ファイルに入っていたデータが students2 テーブルにインポートされています。

4 行目のレコードの remarks カラムには、ちゃんと 「テスト,abc」という値が入っていますね。


以上、MySQL で LOAD DATA 文を使って CSV ファイルをインポートする方法をご説明しました。

© 2024 MySQL 入門