1

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)

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.