I am attempting to load data from the CSV file right into a MySQL database, and observed that a lot of records appear to become missed after i import the file.

The information develops from a Government source, and it is very oddly formatted with single quotes, etc in unusual places. Here is a sample of the record not receiving placed:

"'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 placed:

"'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 am using to load the information is here now:

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;

The format on the table I am loading the information into is the following:

delimiter $$

CREATE TABLE `hospital_quality_scores` (
  `ProviderNumber` varchar(8) NOT NULL,
  `HospitalName` varchar(50) DEFAULT NULL,
  `State` varchar(2) DEFAULT NULL,
  `MeasureCode` varchar(25) NOT NULL,
  `Question` longtext,
  `AnswerDescription` longtext,
  `AnswerPercent` int(11) DEFAULT NULL,
  `NumberofCompletedSurveys` varchar(50) DEFAULT NULL,
  `SurveyResponseRatePercent` varchar(50) DEFAULT NULL,
  `Footnote` longtext,
  PRIMARY KEY (`ProviderNumber`,`MeasureCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Anybody have ideas why this really is happening? It appears that have only from the records are really being placed properly.

Can it be most of your secret is stopping the extra data from being placed?

Search for an archive that's been placed having a ProviderNumber of "'050441'" along with a MeasureCode of "H_HSP_RATING_7_8", for those who have among individuals, then its a replica key problem.

You may want to add "AnswerDescription" towards the primary answer to get round this problem.

Regards,

Dork