LOAD DATA INFILE skipping records

I'm trying to load data from a CSV file into a MySQL database, and noticed that a large number of records seem to be skipped when I import the file.

The data comes from a Government source, and is very oddly formatted with single quotes, etc in unusual places. Here's a sample of a record not getting inserted:

"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_7_8","How do patients rate the hospital overall?","Patients who gave a rating of'7' or '8' (medium)","22","300 or more","37",""

This record, however, does get inserted:

"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_0_6","How do patients rate the hospital overall?","Patients who gave a rating of '6' or lower (low)","8","300 or more","37",""

The SQL I'm using to load the data is here:

mysql> load data infile "c:\\HQI_HOSP_HCAHPS_MSR.csv" into table hospital_qualit
y_scores fields terminated by "," enclosed by '"' lines terminated by "\n" IGNOR
E 1 LINES;

Anyone have any ideas why this is happening? It seems that only have of the recor开发者_如何转开发ds are actually being inserted correctly.

---

After executing the query with failed record mentioned over here.

mysql> load data ....;

execute the foll code

mysql> show warnings; 

It will show the error why the query got failed? and carry on with the next step to solve it.

I did a small test with ur data which worked fine:

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `no` varchar(20) NOT NULL,
  `name` varchar(20) NOT NULL,
  `country` varchar(20) NOT NULL,
  `rating` varchar(20) NOT NULL,
  `rate_desc1` varchar(100) NOT NULL,
  `rate_desc2` varchar(100) NOT NULL,
  `no1` int(11) NOT NULL,
  `desc3` varchar(20) NOT NULL,
  `no2` int(11) NOT NULL,
  `desc4` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

/var/lib/mysql/test/test.csv contianing both of working n non-working records:

1,"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_7_8","How do patients rate the hospital overall?","Patients who gave a rating of'7' or '8' (medium)","22","300 or more","37","" 2,"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_0_6","How do patients rate the hospital overall?","Patients who gave a rating of '6' or lower (low)","8","300 or more","37",""

mysql> load data infile "test.csv" into table test2 fields terminated by "," enclosed by '"' lines terminated by "\n" IGNORE 1 LINES;

result: Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Conclusion: So, i think the difference is only in the datatypes of the columns n see the warnings genereated