MySQL-load data infile- invalid data validation

data validationMySQL

Is it possible to validate the data and load it in a table using load data infile?

This is the query

load data local infile 'C:/Users/970685/Desktop/SFcheck.csv'
into table wow_macro.starting_footage_stg
fields terminated by ','
IGNORE 1 LINES
FIELDS ESCAPED BY \N
(store_id,category_id,footage,plan_id,@merch_id) ;

This is data in CSV:

store_id    category-id footage plan_id merch_id
1           1           3       1       fdfd

All the fields are INT in the table but when I execute the query the value 'fdfd' is not validated but saved as 0. (I have also given not null in the table and have not set any default value)

Best Answer

You can validate and have check the values before inserting. Check the example below:

load data infile ‘path/to/file.csv’ into table TABLENAME fields terminated by ‘,’ (col1,col2,@TRUEFALSECOLVAR) set TRUEFALSECOL=case @TRUEFALSECOLVAR when ‘true’ then 0 when ‘false’ then ‘1’ end;

More load data infile usecases.

Related Question