load data local infile imports only 200k out of 400k records-CodePudding

Hello! I am new to MYSQL so kindly explain in as simple language as possible!

I have a csv with 400k rows and want to import it into mysql. I am using LOAD DATA LOCAL INFILE command for this purpose:

LOAD DATA LOCAL INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Comorbidity Covid-19.csv'
INTO TABLE `comorbidity covid-19`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The issue is that only about 200k records are being imported while the csv contains 400k records. Why is this happening? I executed the command both in the command prompt and in MySql Workbench but both give the same output. Also the date column is not being imported correctly. Instead of dates being displayed it is showing 0000-00-00 in each rows.

PS: OPT_LOCAL_INFILE=1 in manage database connections!

PS : Here is some sample data

What I did was first I created an empty table in the database with respective column types by. I created an empty table with only the column headers by right clicking on tables and selecting create new table option where I selected the proper type for each columns.. Date as of and Start Date were given Date type and so on. Then I executed the above query both in command prompt and workbench to import the rows.

show create table comorbidity gives this result:

CREATE TABLE `comorbidity` (
  `Date as of` date NOT NULL,
  `Start Date` date NOT NULL,
  `State` varchar(20) NOT NULL,
  `Condition group` varchar(50) NOT NULL,
  `Condition` varchar(45) NOT NULL,
  `Age group` varchar(15) NOT NULL,
  `Covid19 deaths` int NOT NULL,
  `Number of mentions` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CodePudding user response：

It may be that the date is not in the correct format that is why it looks wrong. Try to modify the field from excel to a correct format, or perform a DATE() function for the date.

On the subject of importing all the records, check if there is any character that interrupts the execution.

CodePudding user response：

The reason only 200k records were being imported was because I was using:

LINES TERMINATED BY '\n'

When I changed it to:

LINES TERMINATED BY '\r\n'

All 400 k records were imported.