How to filter information when loading a local file?

Question

Is there any way to ignore a certain information when loading a local file into Mysql?

Example: I have a 2 column file: "PHONE" and "VALIDATED". The "VALIDATED" column has the values 'YES' and 'NAO'.

As I do not need the negative cases I wanted to ignore them to save space and decrease the consultation time in the table.

Is there any way to charge only phones with VALIDADO = 'SIM'?

Answer 1

You can import all data to a temporary table and then copy only the desired entries to the destination table.

Example assuming a table called contatos:

CREATE TEMPORARY TABLE contatos_temp LIKE contatos;
LOAD DATA LOCAL INFILE 'meu_arquivo' INTO TABLE contatos_temp;
INSERT INTO contatos (telefone, validado, dt_imp)
SELECT telefone, validado, dt_imp
  FROM contatos_temp 
 WHERE validado = 'SIM';
-- Opcional se você pretende manter a sessão aberta
DROP TEMPORARY TABLE contatos_temp;