MySql file import (LOAD DATA LOCAL INFILE)
I have a table called city:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| country_id | mediumint(9) | NO | MUL | NULL | |
| region_id | bigint(20) | NO | MUL | NULL | |
| city | varchar(45) | NO | | NULL | |
| latitude | float(18,2) | NO | | NULL | |
| longitude | float(18,2) | NO | | NULL | |
| timezone | varchar(10) | NO | | NULL | |
| dma_id | mediumint(9) | YES | | NULL | |
| code | varchar(4) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
I have a simple file (just a test file) to import:
"id","country_id","region_id","city","latitude","longitude","timezone","dma_id","code"
42231,1,833,"Herat","34.333","62.2","+04:30",0,"HERA"
5976,1,835,"Kabul","34.517","69.183","+04:50",0,"KABU"
42230,1,852,"Mazar-e Sharif","36.7","67.1","+4:30",0,"MSHA"
42412,2,983,"Korce","40.6162","20.7779","+01:00",0,"KORC"
5977,2,1011,"Tirane","41.333","19.833","+01:00",0,"TIRA"
5978,3,856,"Algiers","36.763","3.051","+01:00",0,"ALGI"
5981,3,858,"Skikda","36.879","6.907","+01:00",0,"SKIK"
5980,3,861,"Oran","35.691","-0.642","+01:00",0,"ORAN"
I run this command:
LOAD DATA LOCAL INFILE 'cities_test.txt' INTO TABLE city FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Output:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0
No records are inserted and I don't know why.
Any ideas?
Thanks!
Jamie
Answer
Worked it out. Silly mistake.
Had to change this:
LINES TERMINATED BY '\r\n'
To this:
LINES TERMINATED BY '\n'
:-)