How to import data from .dat file to mysql table?

[thara]

I do have a `.dat` file that contain airports data all around the world. So now I need to insert data into `mysql` from this `.dat` file.

This is how I tried it in mysql:

    LOAD DATA LOCAL INFILE '/tmp/airports.dat'
    REPLACE INTO TABLE airports
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (apid, name, city, country, iata, icao, y, x, elevation, timezone, dst, tz_id);

But its not inserting data into mysql and I can get an error when I run above query.

    mysql> LOAD DATA LOCAL INFILE '/tmp/airports.dat' REPLACE INTO TABLE airportsFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (
    apid, name, city, country, iata, icao, y, x, elevation, timezone, dst, tz_id);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
    TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (apid, nam' at line 1
    mysql>

This is the data structure of my `.dat` file:

1,"Goroka","Goroka","Papua New Guinea","GKA","AYGA",-6.081689,145.391881,5282,10,"U","Pacific/Port_Moresby"
2,"Madang","Madang","Papua New Guinea","MAG","AYMD",-5.207083,145.7887,20,10,"U","Pacific/Port_Moresby"
3,"Mount Hagen","Mount Hagen","Papua New Guinea","HGU","AYMH",-5.826789,144.295861,5388,10,"U","Pacific/Port_Moresby"
4,"Nadzab","Nadzab","Papua New Guinea","LAE","AYNZ",-6.569828,146.726242,239,10,"U","Pacific/Port_Moresby"
5,"Port Moresby Jacksons Intl","Port Moresby","Papua New Guinea","POM","AYPY",-9.443383,147.22005,146,10,"U","Pacific/Port_Moresby"
6,"Wewak Intl","Wewak","Papua New Guinea","WWK","AYWK",-3.583828,143.669186,19,10,"U","Pacific/Port_Moresby"
7,"Narsarsuaq","Narssarssuaq","Greenland","UAK","BGBW",61.160517,-45.425978,112,-3,"E","America/Godthab"
8,"Nuuk","Godthaab","Greenland","GOH","BGGH",64.190922,-51.678064,283,-3,"E","America/Godthab"
9,"Sondre Stromfjord","Sondrestrom","Greenland","SFJ","BGSF",67.016969,-50.689325,165,-3,"E","America/Godthab"
10,"Thule Air Base","Thule","Greenland","THU","BGTL",76.531203,-68.703161,251,-4,"E","America/Thule"

And so on upto 8000 of lines.

Hope somebody may help me out.
Thank you.
 

 

 

[benanamen]

See a problem here?

 

mysql> LOAD DATA LOCAL INFILE '/tmp/airports.dat' REPLACE INTO TABLE airportsFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (

    apid, name, city, country, iata, icao, y, x, elevation, timezone, dst, tz_id);

Edited October 30, 2015 by benanamen

[QuickOldCar]

LOAD DATA LOCAL INFILE '/tmp/airports.dat'
    REPLACE INTO TABLE airports
    FIELDS ESCAPED BY '\"'
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\r\n'
    (`apid`, `name`, `city`, `country`, `iata`, `icao`, `y`, `x`, `elevation`, `timezone`, `dst`, `tz_id`)

 

Remove the double quotes in the last record after insert or add an empty line at the end of dat file

Edited October 30, 2015 by QuickOldCar