MySql file import (LOAD DATA LOCAL INFILE)

Question

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

Nicolina

Answer 1

Worked it out. Silly mistake.

Had to change this:

LINES TERMINATED BY '\r\n'

To this:

LINES TERMINATED BY '\n'

:-)

Nicolina

Answer 2

I had the same problem, but I try this, erase the first row

`("id","country_id","region_id","city,"latitude","longitude",
                  "timezone","dma_id","code")` in your file to import.

Now when you run the comand write like this

mysql> LOAD DATA LOCAL 
    INFILE 'cities_test.txt' 
    INTO TABLE city FIELDS TERMINATED BY ','  
    LINES TERMINATED BY '\n';

And that is all.

It worked for me :D

Vow

I encounter the same problem and I am using Windows 10 64-bit version. MySQL Workbench is 64-bit version 8.0.12. My data file shows line feed at the end when opened in Notepad++. I have to take out the first row and use LINE TERMINATED BY '\n' and it works. – Else

Answer 3

I had same issue on mac, Try this if you are using mac

LOAD DATA INFILE 'sqlScript1.txt' INTO TABLE USER
FIELDS TERMINATED BY ','  LINES STARTING BY '\r';

Ourselves

Answer 4

For me, what worked on a mac was

LOAD DATA LOCAL 
    INFILE 'cities_test.txt' 
    INTO TABLE city FIELDS TERMINATED BY ','  
    LINES TERMINATED BY '\r';

Since Macs use carriage return for its line break you must use '/r'

Cindicindie

This effectively restates the currently accepted answer from six years ago. What does yours add that might be useful to others? – Confiture

Short explanation of why one has to use '/r' instead of '/n' on a mac – Cindicindie

OS X (or rather macOS) hasn't used \r as a newline character since the release of OS X 10.0 back in 2001 (see this question over on Super User) – Confiture

Got it, then why is it still used in some types of SQL on OSX? – Cindicindie

Which SQL dialect are you using? – Confiture

I am using MySql . – Cindicindie