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?

Solution 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.

Solution 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.

Solution 3

I had the same problem and I solved using doble quotes "\r\n" instead '\r\n'

Solution 4

For those interested, I had the same issue as described above but it had nothing to do with the LINES TERMINATED BY assignment, primary key violations or wrong character set.

I started getting this error after upgrading to MySQL 8 and my problem ended up being the column names I was sending via the col_name_or_user_var option. Originally I had extra columns in the import file that I wasn't using, and this caused no issue with the original implementation. After upgrading, however, I had to add variable entries for these columns to get it working and getting more than one row imported (I did not have access to change these columns in import.csv). With an import file like this:

FIELD_1, FIELD_2, FIELD_3, FIELD_4, FIELD_5

I had to go from:

LOAD DATA INFILE 'import.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(FIELD_1, FIELD_2, FIELD_3)

to:

LOAD DATA INFILE 'import.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(FIELD_1, FIELD_2, FIELD_3, @FIELD_4, @FIELD_5)

View more solutions

Comments

[ESG]

I agree with the lack of auto increment. I wouldn't be surprised if it tried to insert 0 for every row, and failed for all but 1 due to a key conflict.

[Andrew]

What did the trick? Really wish I knew the answer to this question. Auto-increment doesn't seem to be the issue for me

[Andrew]

For those of your struggling with this issue - this solved my issue: stackoverflow.com/questions/1268273/…