Search code examples
mysqlmysqlimport

Skipped lines on mysql load local data

There is an example of my csv file:

direction,latitude,longitude,metrictimestamp,odometer,routecode,speed,device_deviceid,vehicle_vehicleid
180,-3.724404,-38.557694,20180201025934,161245809,0,0,148469,33089
0,-3.878595,-38.533493,20180201025955,19030291,0,0,8155064,34489
135,-3.744851,-38.545571,20180201025959,55697826,0,3,134680,32040

And there is the execution of the import query:

enter image description here

Any ideas of why this lines are being skipped??

Solution

  • You can use SHOW WARNINGS after LOAD DATA to show what went wrong. Please note that by default it doesn't show all 35 million warnings but just a first few.

    That said, your data doesn't include anything for 'id' so that's probably at least part of the issue. Another is that you don't enclose the fields in double quotes but do have ENCLOSED BY '"'

    See LOAD DATA documentation to see how you can choose which fields to populate:

    LOAD DATA LOCAL INFILE 'C:/Users/Public/Downloads/Dados.csv'
    INTO TABLE data
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
(direction, latitude, longnitude, metrictimestamp,
    odometer, routecode, speed, deviceid, vehicleid)

    As id is AUTO INCREMENT, it will be populated automatically.