Why does LOAD DATA INFILE not insert any data?

0 votes

1 answer

1285 views

                          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 are 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?
                        

Asked by mic255 (1 rep)

Mar 3, 2017, 04:02 PM
Last activity: Jan 10, 2025, 02:09 PM

Why does LOAD DATA INFILE not insert any data?

Related Questions