I need to load data that is coming from a csv file to a particular table. I am loading 5 fields of the csv file into the table. I need to apply regular expression for a particular field value in the csv file. IF it doesnt match i need to reject that record. Is it possible ?
This is my load data query:
LOAD DATA LOCAL INFILE ''/test.csv''
INTO TABLE TEST_TABLE FIELDS
TERMINATED BY '',''
LINES TERMINATED BY ''\n''
(@FIELD1,@FIELD2,@FIELD3,@FIELD4,@FIELD5)
SET
FIELD1=STR_TO_DATE(@FIELD1,''%d-%m-%Y''), FIELD2=nullif(@FIELD2,''''),
FIELD3=nullif(@FIELD3,''''), FIELD4=nullif(@FIELD4,''''),
FIELD5=nullif(@FIELD5,'''');
If the values that is coming in field4 in csv file is equal to either 200 or 300, i need to consider that record and load other values otherwise i need to reject the record.
Sample file::
1),234232323,STATUS,200,33
2),45454545,STATUS,300,33
3),646546445,STATUS,100,33
here 1st and 2nd record should be considered and 3rd record should be rejected.