MYSQL: Display Skipped records after LOAD DATA INFILE?

sql mysql load-data-infile
Nate Murray picture Nate Murray · Apr 30, 2010 · Viewed 30.2k times · Source

In MySQL I've used LOAD DATA LOCAL INFILE which works fine. At the end I get a message like:

Records: 460377  Deleted: 0  Skipped: 145280  Warnings: 0

How can I view the line number of the records that were skipped? SHOW warnings doesn't work:

mysql> show warnings;
Empty set (0.00 sec)

Answer

oabarca picture oabarca · Feb 22, 2014

If there was no warnings, but some rows were skipped, then it may mean that the primary key was duplicated for the skipped rows.

The easiest way to find out duplicates is by openning the local file in excel and performing a duplicate removal on the primary key column to see if there are any.

Related questions

LOAD DATA LOCAL, How do I skip the first line?

I'm trying to Load a CSV file into my MySQL database, But I would like to skip the first line. I fact It contains the name of my columns and no interesting data. Here is the query I'm using: LOAD …

Read More
PHP - Import CSV file to mysql database Using LOAD DATA INFILE

I have a .csv file data like that Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude "Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,, "Sep-18-2013 01:54:14 PM","Unknown","outgoing …

Read More
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from a CSV file into an existing database table. Here is an example SQL statement: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY …

Read More