MySQL LOAD DATA LOCAL INFILE only imports a single row

Question

We have a CSV file with thousands of records in it. I want to import these rows into a MySQL table via phpmyadmin. here is the command used:

load data local infile '/var/www/html/deansgrads_201280.csv' 
into table ttu_nameslist
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(firstname, middlename, lastname, city, county, state, termcode, category)

There is an ID field in the table that is set to auto-increment. When we execute this SQL only the first line is imported into the table.

Input data file lines:

"Aaron","Al","Brockery","Cookeville","Putnam","TN","201280","deanslist"
"Aaron","Dan","Mickel","Lebanon","Wilson","TN","201280","deanslist"

Table structure:

CREATE TABLE `ttu_nameslist` (
  `id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `middlename` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `county` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  `termcode` varchar(6) NOT NULL,
  `category` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

What am I doing wrong why does it quit after adding one row?

Answer 1

You say that the ID field has the AUTO_INCREMENT attribute, but there's no mention of it in the CREATE TABLE statement. This is part of the problem.

The other part is those truncation warnings. Some of the rows in the CSV file probably contain data that is too long to fit inside the columns. Increase the size of those text columns to a bigger value (let's say 200) and try again.

Are you absolutely sure that the CSV file is valid ? (a.k.a. each row has the same number of values etc.). You should probably check if those strings contain commas (,), although that shouldn't be an issue.

Answer 2

Why is MySQL LOAD DATA INFILE command only loading one row?

What is happening is you load the first column fine, then when you load the second and it fails because is violates a unique index constraint.

MySQL LOAD DATA LOCAL INFILE will enforce your unique indexes on columns and then skip any offending duplicate rows without doing anything about it, not even logging a warning!

How to reproduce this phenemenon:

1. Create a table with an int and varchar column:

   mysql> create table foo(id INT, mytext VARCHAR(255));
   Query OK, 0 rows affected (0.02 sec)
   

2. Add a unique constraint on the varchar column:

   mysql> alter table foo add constraint my_epic_constraint unique(mytext);
   Query OK, 0 rows affected (0.02 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   

3. Create your input text file /tmp/foo.txt, delimited by tabs, which violates the unique constraint:

   1   fred
   2   fred
   

4. Try importing it:

   mysql> load data local infile '/tmp/foo.txt' into table foo fields 
          terminated by '\t' lines terminated by '\n' (@col1,@col2) 
          set id=@col1, mytext=@col2;
   
   Query OK, 1 row affected (0.01 sec)
   Records: 2  Deleted: 0  Skipped: 1  Warnings: 0
   

BAM! There's your problem: why is it only importing only one row? Because you have a unique key constraint on it which the lines in the data file violated.

Solutions:

1. Remove the unique key constraint on your table and try again.

2. Go into the text file and remove the duplicate rows that violate the unique constraint.