Filter the data being loaded with MySQL LOAD DATA command

Question

I am using the following command.

LOAD DATA  INFILE 'source.txt'
INTO TABLE tblData
CHARACTER SET utf8
FIELDS TERMINATED BY '\t';

This works fine, but the source.txt that I get is an 800MB file with only 10% rows that are required by me. I cannot do the filtering on the text file before load. I can filter my results based on the value of one specific column. Is there a way I can specify this condition in my load statement so only the required rows are loaded.

Answer 1

Nope, at least not with LOAD DATA INFILE.

You can however create a script that parses your file and only inserts the records that matches your criteria

Answer 2

You can have your script create a temporary file, call LOAD DATA INFILE into the temporary file, and then filter the temporary file into the final destination.

CREATE TEMPORARY FILE temp LIKE tblData;
LOAD DATA  INFILE 'source.txt'
    INTO TABLE temp
    CHARACTER SET utf8
    FIELDS TERMINATED BY '\t';
INSERT INTO tblData (field1, field2)
    (SELECT field1, field2 FROM temp WHERE some condition);

If your system is configured to make sure the temporary file stays in memory, you should still get good performance.