Import and export CSV file into MySQL Table

Clean up csv for file like below

    "Email"
    "abc@exammle.com"
    "xyz@kdjfi.com.np"
    "oioi@uyii.net"
    ...  ## note not commas and carriage returns because csv was imported from windows os

move the files to /var/lib/mysql-files  directory 

sed -i  's/$/,/g'  Aggregate-SentList-Introduction.csv 

sed -i  's/\r//g'  Aggregate-SentList-Introduction.csv 

First of all create necessary table for example `table name = gel `

mysql> create table gel(email VARCHAR(255));

Now import the csv file 

mysql> LOAD DATA LOCAL INFILE '/home/user1/Desktop/temporary/gel.csv'
INTO TABLE store.gel
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

ignoring 1st row because it contained the column name for example

"Email List"
"jack@example.com"
"jill@abc.com"
"wfpk@unitki.com"
....

...

mysql> LOAD DATA LOCAL INFILE '/home/user1/Desktop/temporary/sentlog1.csv'  INTO TABLE store.sent1  FIELDS TERMINATED BY ','  ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 3376 rows affected (0.66 sec)
Records: 3376  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/home/user1/Desktop/temporary/sentlog2.csv'  INTO TABLE store.sent2  FIELDS TERMINATED BY ','  ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 8732 rows affected (1.71 sec)
Records: 8732  Deleted: 0  Skipped: 0  Warnings:


EXPORT sql select result

mysql> SELECT email FROM blocks WHERE email NOT IN (select mail from `TABLE 2`) INTO OUTFILE '/var/lib/mysql-files/filteredaug24.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 67782 rows affected (0.62 sec)
If file exist then it will give error you need to remove it first

Useful query
SELECT emails FROM sent
LEFT JOIN blocks ON sent.emails=blocks.email
WHERE blocks.email IS NULL;

OR

SELECT emails FROM sent
WHERE emails NOT IN ( select email from blocks )
INTO OUTFILE '/var/lib/mysql-files/filteredaug24-5.csv'  LINES TERMINATED BY '\n' ;