動かざることバグの如し

3分経てば忘れそうなことをメモします

駅データ.jpのCSVファイルをMySQLにブチこむ

テーブルの作成

create table rawdata(station_cd int primary key not null, station_g_cd int not null, station_name varchar(100) not null, station_name_k varchar(100) not null, station_name_r varchar(100) not null, line_cd int not null, pref_cd int not null, post varchar(100) not null, address varchar(300) not null, lon float not null, lat float not null, open_ymd varchar(100) not null, close_ymd varchar(100) not null, e_status int not null, e_sort int not null);

確認

mysql> desc rawdata;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| station_cd     | int(11)      | NO   | PRI | NULL    |       |
| station_g_cd   | int(11)      | NO   |     | NULL    |       |
| station_name   | varchar(100) | NO   |     | NULL    |       |
| station_name_k | varchar(100) | NO   |     | NULL    |       |
| station_name_r | varchar(100) | NO   |     | NULL    |       |
| line_cd        | int(11)      | NO   |     | NULL    |       |
| pref_cd        | int(11)      | NO   |     | NULL    |       |
| post           | varchar(100) | NO   |     | NULL    |       |
| address        | varchar(300) | NO   |     | NULL    |       |
| lon            | float        | NO   |     | NULL    |       |
| lat            | float        | NO   |     | NULL    |       |
| open_ymd       | varchar(100) | NO   |     | NULL    |       |
| close_ymd      | varchar(100) | NO   |     | NULL    |       |
| e_status       | int(11)      | NO   |     | NULL    |       |
| e_sort         | int(11)      | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

よみこみ

LOAD DATA LOCAL INFILE "/home/user/station20141030free.csv" INTO TABLE `rawdata` FIELDS TERMINATED BY ',' IGNORE 1 LINES;

IGNORE 1 LINES;付けないと最初の一行目を読み込もうとしてエラーになるので注意