0

I have a large .csv file with my customers Name:Email

I have another large list of emails that are part of this list and only want to keep those. How can I remove all contacts from the .csv file that are not in X.txt?

  • Expand your post and put a few lines of your originial of your file and put target file(removed some fields) – PersianGulf May 18 '15 at 15:38
0

If you don't need the email to be in a particular field of the .csv file, and only need to print those lines of the file that contain one of the target emails, you could just use grep:

grep -wFf emails.txt file.csv > newfile.csv

If they do need to be in a specific field, use @ChrisDown's solution. Note, however, that csv files can be quite complex. For example, this is a valid csv line:

field1,"field 2, which can contain commas, is here", field3

For such, more complex files, you will need to use a csv parser to deal with them correctly. For example, in Perl, you can use Text::CSV:

  1. Install cpanm (if you use Perl, you'll thank me later)

    $ sudo apt-get install cpanminus
    

    If you're not on a Debian based system, you should be able to install it using your distribution's package manager.

  2. Install the Text::CSV module

    $ sudo cpanm Text::CSV
    
  3. Parse your file

    $ perl -MText::CSV -le '
    
        BEGIN{
            open($f,"emails.txt"); 
            while(<$f>){chomp; $k{$_}++;}
        } 
        $csv=Text::CSV->new({binary=>1}); 
        while ($row = $csv->getline(STDIN)){
          print join",", @$row if defined($k{$row->[2]})
        }' < file.csv
    
1

Assuming that the e-mails in X.txt are one per line, you can build an array, check it for existence, and then print out the matching lines:

$ awk -F: 'FNR==NR { a[$2] = $0; next } ($1 in a) { print a[$1] }' customers.csv X.txt
Foo Bar:foo@bar.com
Baz Qux:baz@qux.com

FNR==NR will only be true when in the first file. Here are the files I used to test this:

$ cat customers.csv 
Foo Bar:foo@bar.com
Baz Qux:baz@qux.com
Wibble Wobble:wibble@wobble.com
$ cat X.txt 
foo@bar.com
baz@qux.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.