Uploading csv file using filters

Question

Am trying to upload CSV data to MYSQL table using the below query.It's running successfully. The CSV file having 20 million data. But still now I have a problem to upload the data.

My CSV file structure like this :

Name phone_no     DND
xxx  99934034343  A
xxx  99934034345  D
xxx  99934034346  A
xxx  99934034347  D

I want to insert the active no "A" only using the below command.

LOAD DATA LOCAL INFILE '/root/782012_23.csv'
INTO TABLE tbl_dndno
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Expected record like this

Name phone_no     DND
 xxx  99934034343  A
 xxx  99934034346  A

Answer 1

I guess your command is working fine but filling your table with 4 rows instead of the expected 2.

LOAD DATA IN FILE documentation seem not to support what you are asking for, it can only exclude line by number, so if you have a way to know the line numbers while creating this csv file you could use this feature. Otherwise there are 2 workarounds:

You can filter your file before loading it (outside of mysql) with (on Linux)

grep "D$" /root/782012_23.csv > filteredfile; LOAD DATA LOCAL INFILE 'filteredfile' ...

or filter after the loading via SQL:

your_command;mysql -u user -ppassword "DELETE FROM yourtable WHERE DND = 'A'";