1. データ元のファイルを作成する
1.1. test.csvの作成
# touch /tmp/test.csv
1.2. test.csvの編集
# vi /tmp/test.csv
1,"aaa","りんご"
2,"bbb","いちご"
3,"ccc","寿司"
4,"ddd","テンプラ"
5,"eee","ﾃﾝﾌﾟﾗ"
2. テーブルを作成する
2.1. MySQLに接続
# mysql --user=ユーザ名 --password=パスワード DB名
2.2. テーブルの作成
mysql>create table csv
(cid int,
sname varchar(100),
vname varchar(100)
);
2.3. 構造確認
# desc csv;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| cid | int(11) | YES | | NULL | |
| sname | varchar(100) | YES | | NULL | |
| vname | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3. CSVをインポート
3.1. カラム名を全て指定する場合
mysql> LOAD DATA INFILE '/tmp/test.csv' INTO TABLE csv FIELDS TERMINATED BY ',' ENCLOSED BY '"' (@not,sname,vname);
3.2. 特定のカラムを指定する場合
mysql> LOAD DATA INFILE '/tmp/test.csv' INTO TABLE csv
FIELDS TERMINATED BY ',' ENCLOSED BY '"' (@not,@not,vname);
4. エクスポート
4.1. selectコマンドでエクスポートする
mysql> SELECT * FROM csv INTO OUTFILE "/tmp/etest.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
LOAD DATA INFILE 'file.txt' INTO TABLE csv(dummy=@sname, dummy1=@vname) "/tmp/test.csv"
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n;
mysql>quit;
4.2. データを確認する
# cat /tmp/etest.csv
1,"aaa","りんご"
2,"bbb","いちご"
3,"ccc","寿司"
4,"ddd","テンプラ"
5,"eee","ﾃﾝﾌﾟﾗ"
