Search code examples
mysqlmysql-workbenchmysql-error-1064

How to import datetime field coded with `NULL` keyword in MySQL?

I have a table like as shown below

enter image description here

Please note that I already referred to this post and it's not duplicate

When I try to upload the data in csv file using MySQL workbench, I am able to see that it ignores rows with NULL values for datetime field. Meaning it only copies rows 2,4 and 5

Please find the error message below

  • Row import failed with error: ("Incorrect datetime value: '' for column 'Date_entry' at row 1", 1292)

Please note that Date_entry is an index column

alter table dummy
      add index ADMISSIONS_IDX01 (Subject),
      add index ADMISSIONS_IDX02 (Date_entry);

Table definition is like this:

Subject MEDIUMINT UNSIGNED NOT NULL,
Date_entry DATETIME,

In create statement, I have a line like as shown below

Date_entry= IF(@Date_entry='', NULL, @Date_entry),

Can you help me fix this error?

Solution

  • In MySQL version >=8.0 by default local_infile is off. To load you need to first enable it.

    set global local_infile = on;

load data local infile 'E:so.csv' into table dummy
fields terminated by ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Subject,@Date_entry)
set Date_entry=if(@Date_entry='',null, @Date_entry) ;

    It will insert the data. If it throws error then share the result of sql_mode.

    select @@sql_mode;

    Note: Make sure that the csv on Date_entry column is yyyy-mm-dd hh:mm:ss format as MySQL does, btw in your csv it is not in db format. I have tested the above case in MySQL Workbench.