0

I have this project I am working on, below is the sample of the text file I need to load into my database.

routes.txt:

"route_id","origin","destination","distance"
1,"ABE","ATL",692.000
2,"ABE","DTW",425.000
3,"ABE","ORD",655.000

flights.txt:

"carrier","flight_number","route_id","departure_time","arrival_time"
"AA",43,1051,"1100","1438"
"AA",43,1182,"1523","1730"
"AA",44,3477,"0710","1527"
"AA",45,1921,"1830","2152"

Here are the tables I created:

create table flights( 
  route_id INT NOT NULL,  
  carrier  char(2)  NOT NULL, 
  flight_number int  NOT NULL,  
  departure_time date NOT NULL, 
  arrival_time date NOT NULL,  
  primary key (route_id),  
  index(route_id) )

create table routes( 
  route_id INT NOT NULL, 
  origin   char(3)  NOT NULL, 
  destination  char(3)  NOT NULL, 
  distance  decimal(8,3) NOT NULL, 
  primary key (route_id), 
  index(route_id) )

Here is the the commands I executed to load the data:

LOAD DATA infile '/var/lib/mysql-files/routes.txt'  INTO TABLE routes     
  fields terminated by ','   lines terminated BY '\n\r' IGNORE 1 LINES 
  (route_id,origin,destination,distance);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

from the result above you see the data are not loaded.

What could be the reason?

I am using MySQL 5.7

  • 1
    You seem to be using Linux; are you sure aboutlines terminated BY '\n\r'? – mustaccio Mar 3 '17 at 16:11
  • 1
    A PRIMARY KEY is a UNIQUE key is an INDEX. Don't redundantly say index(route_id). – Rick James Mar 4 '17 at 4:01
0

Use LINES TERMINATED BY '\n' or just leave it out altogether. '\n' is the linefeed character on Linux, and with '\n\r' it will never see a linefeed, so the entire file looks like one long line, and you told it to ignore 1 line, so it sees nothing to insert.

You also need "OPTIONALLY ENCLOSED BY '"'. Try something like this:

mysql> LOAD DATA LOCAL INFILE 'routes.txt' 
    INTO TABLE routes FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM routes;
+----------+--------+-------------+----------+
| route_id | origin | destination | distance |
+----------+--------+-------------+----------+
|        1 | ABE    | ATL         |  692.000 |
|        2 | ABE    | DTW         |  425.000 |
|        3 | ABE    | ORD         |  655.000 |
+----------+--------+-------------+----------+
3 rows in set (0.00 sec)

Obviously, leave off LOCAL if using a file on a remote server and be sure the file is readable by MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.