conditionally ignore rows load data mysql

Question

I am loading a large data set (60 gigs) into a database. There are some records that can be skipped because they contain values that are missing. How do I tell MySQL to skip rows that contain certain (missing in this case) values? For example, my file looks like this

Value1, Value2
1,2
3,4
,5
9,10

The third row can be skipped and not loaded. I know that I can load everything and then just delete it but processing a 60 gig file takes a long time so I want to save on the computing power.

Thanks

Answer 1

If 'Value1' is a unique field, then you can play with an IGNORE option. For example:

CREATE TABLE table1(
  Value1 INT(11) NOT NULL,
  Value2 INT(11) DEFAULT NULL,
  PRIMARY KEY (Value1)
);

LOAD DATA INFILE 'file.txt' 
  IGNORE -- forces to ingore existed records
  INTO TABLE table1
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\r\n'
  IGNORE 1 lines
  (@var1, Value2)
  SET Value1 = IF(@var1 = 0, 1, @var1)

'IF(@var1 = 0, 1, @var1)' helps us to change missing values '0' with EXISTED KEY VALUE '1', and these records will be ignored.