LOAD DATA LOCAL INFILE not importing any records

Question

I am working on a project that requires importing csv data into a mysql table using Workbench. I tried running the command below. I am not getting any errors, however no records are being imported in to the table. Note, I tried changing the line terminated by to \n , also tried \r, which resulted in the same outcome.

I appreciate any suggestions on how to resolve.

This is the query I tried:

LOAD DATA LOCAL INFILE 'data-to-import.csv' INTO TABLE db.t1
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(id,age,spns,pns,spns2,sns,ps,ss,gid,term,band)

data-to-import.csv

 ,18,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,19,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,20,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,21,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,22,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,23,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,24,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,25,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,26,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,27,0.455,0.62,0.774,1.083,2.408,3.702,1,10,1
 ,28,0.455,0.62,0.774,1.088,2.416,3.702,1,10,1
 ,29,0.455,0.62,0.792,1.121,2.489,3.702,1,10,1

db.t1 definition

   CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `spns` decimal(4,4) NOT NULL,
  `pns` decimal(4,4) NOT NULL,
  `spns2` decimal(4,4) NOT NULL,
  `sns` decimal(4,4) NOT NULL,
  `ps` decimal(4,4) NOT NULL,
  `ss` decimal(4,4) NOT NULL,
  `gid` int(11) NOT NULL,
  `term` int(11) DEFAULT NULL,
  `band` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

In looking at your SQL, it seems like you might have a problem with your line terminator.

Try this:

LOAD DATA LOCAL INFILE 'data-to-import.csv' INTO TABLE db.t1
COLUMNS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(id,age,spns,pns,spns2,sns,ps,ss,gid,term,band)

If your file comes from Windows, the line terminator is CR/LF. You have it as LF/CR.

If the file comes from *nux, your line terminator is just a line feed in which case your SQL would look like this:

LOAD DATA LOCAL INFILE 'data-to-import.csv' INTO TABLE db.t1
COLUMNS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(id,age,spns,pns,spns2,sns,ps,ss,gid,term,band)

Hope this helps!

---

I just ran the following code:

drop schema if exists db;
create schema db;
use db;

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `spns` decimal(4,4) NOT NULL,
  `pns` decimal(4,4) NOT NULL,
  `spns2` decimal(4,4) NOT NULL,
  `sns` decimal(4,4) NOT NULL,
  `ps` decimal(4,4) NOT NULL,
  `ss` decimal(4,4) NOT NULL,
  `gid` int(11) NOT NULL,
  `term` int(11) DEFAULT NULL,
  `band` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOAD DATA LOCAL INFILE 'c:/temp/data-to-import.csv' INTO TABLE db.t1
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(id,age,spns,pns,spns2,sns,ps,ss,gid,term,band);

SELECT * FROM t1;

I copied the data from what you posted and put it into my temp folder. Worked fine.

First of all, the data you posted did NOT have a header line so I am not sure why you have IGNORE 1 LINES in there.

In addition, try putting a path on the file name like I did and see if that works.

If that doesn't, please let me know where the file originated. It could still be a line terminator issue.

---

OK, so based on our Chat, you are running on Mac OSX and the line terminator is '\r'. Just use that and you should be good to go.