LoginSignup
12

More than 5 years have passed since last update.

mysql の load data local infile でcsvファイルを読み込ませる時の注意

Last updated at Posted at 2017-11-10

MySQLにcsv(RFC4180)フォーマットのファイルを読み込ませる

mysqlにはファイルからデータを取り込むために load (local) data というクエリが用意されています。
このクエリでCSVファイルを読み込むには以下の様なクエリを使います。

load data local infile 'text.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';

上記はファイル内の
- データのの区切り文字は ,
- データの囲い文字は "
- 囲い文字をエスケープする文字は "

という意味で、これらを指定することによりRFC4180 フォーマットのファイルをテーブルに読み込ませる事が出来ます。

割りと酷い内容のファイルでも読み込ませる事が出来る

例えば

mysql > desc load_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| text1 | varchar(256) | YES  |     | NULL    |       |
| text2 | varchar(256) | YES  |     | NULL    |       |
| text3 | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

こんなテーブルがあって、
このテーブルに普通のCSVパーサーではエラーになる様なファイル

hunk_1.csv
aaaaaa,bbb""bbbb,cccccccc
xxxxxx,yyy"yyyy",dddddddd

を読み込ませても意外と取り込んでくれます。

mysql> load data local infile 'hunk_1.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';
Query OK, 2 rows affected (0.05 sec)
mysql> select * from load_test;
+-----------+--------------+--------+
| text1     | text2        | text3  |
+-----------+--------------+--------+
| aaaaaaaaa | bbb"bbbb     | cccccc |
| xxxxxx    | yyyyy"yyyyy" | zzzzzz |
+-----------+--------------+--------+

"で始まるデータが有ると読み込みをしくじる

どうも (行頭または) 区切り文字囲い文字 と文字が続くと、それ以降に現れる 囲い文字区切り文字 (またはEOF)までを一つのデータとみなす仕組みの様で、行頭またはデータの最初に 囲い文字 があって、データの最後に 囲い文字 がない場合はデータの読み込みに失敗します。

hunk_2.csv
aaaaaa,"bbbbbbb,cccccccc
xxxxxx,yyyy"yyy,dddddddd
mysql> load data local infile 'hunk_2.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from load_test;
+----------------------------+---------------------------------------------+--------+
| text1                      | text2                                       | text3  |
+----------------------------+---------------------------------------------+--------+
| aaaaaa                     | "bbbbbbb,cccccccc
xxxxxx,yyy"yyyy,dddddddd
 | NULL   |
+----------------------------+---------------------------------------------+--------+

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
12