mysql - Ignore certain information when loading a local file in the DB?

Question

Is there a way to ignore certain information when loading a local file in the DB?

Example: I have a file with 2 columns "PHONE" and "VALIDATED" where a column of "VALIDATED" receives the status of 'YES' or 'NO'.

As I don't need negative cases, I wanted to ignore them to save space and improve query times.

Is there any way for me to load only phones where VALIDATED = YES?

DB: MYSQL 6.3

Import: LOAD DATA LOCAL INFILE

question from:https://stackoverflow.com/questions/65838337/ignore-certain-information-when-loading-a-local-file-in-the-db

Answer 1

Crossposting a translation of my original answer at Stack Overflow in Portuguese:

It's always possible to load all data to a temporary table and later copy only desired rows to the destination table.

Assuming a table called contacts:

CREATE TEMPORARY TABLE contacts_temp LIKE contacts;
LOAD DATA LOCAL INFILE 'my_file' INTO TABLE contacts_temp;
INSERT INTO contacts (phones, validated, dt_imp)
SELECT phone, validated, dt_imp
  FROM contacts_temp 
 WHERE validated = 'YES';
-- drop statement is only useful if you are planning to keep the
-- current session open
DROP TEMPORARY TABLE contacts_temp;