CODEWITHSUNDEEP
mysqlsqlcsvload-data-infile
nalyr
nalyr

Reputation: 151

Set null value in CSV file to 0 in mysql

I am trying to import data from a CSV file to MySQL. I have a column called attendance whereby there is a possibility that it is a null value in the excel file. Therefore, when importing the data to MySQL, I want to convert these null value in the excel file to a 0 value in MySQL. However, I keep getting an error called "Incorrect Integer value" for the attendance column. I was wondering is there issue with my definition in this line:

SET
attendance = NULLIF(@one, "0");

SQL:

CREATE TABLE IF NOT EXISTS students(
   id INT AUTO_INCREMENT,
   name DATE,
   course INT,
   attendance INT,
   PRIMARY KEY (id)
);


LOAD DATA INFILE
'C:/Users/ben/OneDrive/Desktop/studentslist.csv'
INTO TABLE students

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY "\n"
IGNORE 1 ROWS
(id, name, course, @dummy, @one)

SET 
attendance = NULLIF(@one, "0");

Upvotes: 0

Views: 393

Answers (0)

Related Questions