LOAD txt file DATA INFILE issues 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
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&gt; 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&gt; 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.