Geek asks and answers

MySQL LOAD DATA LOCAL INFILE imports only one row

We have a CSV file with thousands of records. I want to import these rows into a MySQL table via phpmyadmin. here the command is 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)

The table shows the identifier field for which auto-increase is set. When we execute this SQL, only the first row is imported into the table.

Data input 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 go away after adding one line?

+5
source share
3 answers

You say that the ID field has an attribute AUTO_INCREMENT, but this is not mentioned in the instruction CREATE TABLE. This is part of the problem.

- . CSV, , , . ( 200) .

, CSV ? (a.k.a. ..). , , (,), .

+6

MySQL LOAD DATA INFILE ?

, , , , , .

MySQL LOAD DATA LOCAL INFILE , , , !

:

  • int varchar:

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

  • varchar:

    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

  • /tmp/foo.txt, , :

    1   fred
2   fred

  • :

    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! : ? , .

:

  • .

  • , .

+5

I had the same problem and I decided to use doble quotes "\ r \ n" instead of '\ r \ n'

+1
source

More articles:


All Articles