-1

I am trying to delete all emails that contain the word hotmail in the third field from my .txt file. I am currently trying to use the following but it only deletes some lines. I am not sure why

sed -i '/^[^,]*,[^,]*,[^,]*hotmail/d' *.txt

Below is one of the lines that where not removed

"field1","field2.","email@hotmail.com","whoeditedoutn.com","NeditedoutOW.COM|NeditedoutW.COM","editedout",""
"foo,bar","baz,qux","email@hotmail.com","whoeditedoutn.com","NeditedoutOW.COM|NeditedoutW.COM","editedout",""

Could someone give me the correct command please? Also if possible a command that word with both upper case and lower case hotmail or HoTmAiL or any other variant.

  • That command worked fine with above line, can you give full input? – cuonglm Jun 24 '15 at 14:33
  • How can I give you that? Sorry I am new – Teddy291 Jun 24 '15 at 14:38
  • @Teddy291 Write it in the question. – 123 Jun 24 '15 at 14:39
  • The file is too large I printed some lines pastebin.com/DgZ4w33T if you run that line 17 still has hotmail – Teddy291 Jun 24 '15 at 14:50
  • @Teddy291 Thats because it's not the thrid field on that line as the second one contains a comma,you will likely need to use a CSV parser to get consistent accurate results. – 123 Jun 24 '15 at 14:58
2

Since this is a CSV file, fields 1 and 2 may contain commas. Thus regular expression matching will not work. You need a proper CSV parser. Here's an example

ruby -rcsv -ne 'row = CSV::parse_line($_); puts $_ unless row[2] =~ /hotmail/i' file

update: that ruby command indeed does not alter the file. Neither does your sed command above. To save the changes back to the file, use the -i option. With your pastebin data:

wc -l file
ruby -rcsv -i -ne 'row = CSV::parse_line($_); puts $_ unless row[2] =~ /hotmail/i' file
wc -l file
  22 file
  20 file

Perl also has a CSV module, not standard though, get it from CPAN -- error handling not implemented

perl -MText::CSV -le '
  $csv = Text::CSV->new({ binary=>1, always_quote=>1 });
  open $fh, "<", shift(@ARGV);
  while ($row = $csv->getline($fh)) {
    $csv->print(STDOUT, $row) unless $row->[2] =~ /hotmail/i;
  }
' file
  • Hello, this did not work sorry. I tested it with pastebin.com/DgZ4w33T input. Also I am doing this for lots of files so I would rather it not print all the text on screen – Teddy291 Jun 24 '15 at 15:05
  • So did I. It output 20 lines, removing the 2 hotmail entries on lines 17 and 20. – glenn jackman Jun 24 '15 at 15:06
  • Not for me :( do I need anything else installed other than ruby? I am using CentOS 6 – Teddy291 Jun 24 '15 at 15:08
  • Probably not. "csv" is part of the standard library. What version of ruby do you have? – glenn jackman Jun 24 '15 at 15:09
  • I just tried it on my Ubuntu VPS also and it did not work. ruby 1.8.7 and ruby 1.9.3p484. The file is a .txt but I also tried converting to csv and it still doesn't remove – Teddy291 Jun 24 '15 at 15:14
2

As you asked for a sed solution,

sed -n -e '/^"[^,]*","[^"]*",".*@hotmail/Ip' file

Here I am just printing the lines that would be deleted. revert to your original options to do exactly what you want. change /Ip to /Id and change -n back to -i

/I provides case insensitive search

The second field uses [^"] instead of not comma so that fields with commas inside are not split.

and I have added the quotes onto the fields and added @ before domain name to make it look more like an email address.

Update: This version ensures that @hotmail.com is followed by 2 email addresses. ie is first on the line.

This searches for a hotmail address at third column

sed -n -e '/^"[^"]*","[^"]*",".+@hotmail.com",.+@.+,.+@.+$/Ip' file

This one for hotmail any where in third col so works with your test data in pastebin.

sed -n -e '/^"[^"]*","[^"]*",".*hotmail.+",.+@.+,.+@.+$/Ip' file

Update2:

I've simplified the regex to :

sed -n -e '/^("[^"]*",){2}"[^"]*hotmail[^"]*"/Ip'
  • sed -n -e '/^"[^,]*","[^"]*",".*@hotmail/Ip output.txt' returns the error sed: -e expression #1, char 34: extra characters after command – Teddy291 Jun 24 '15 at 15:40
  • sorry, I added file incorrectly during my paste, will correct. – X Tian Jun 24 '15 at 15:42
  • Thank you. That worked by showing the lines that have hotmail but I removed -n and replace /Ip with /Id but that just printed everything, it did not remove any lines containing hotmail - sed -e '/^"[^,]*","[^"]*",".*@hotmail/Id' output.txt was the full command – Teddy291 Jun 24 '15 at 15:45
  • arh, You originally used -i option to edit in place, add -i (I like to keep original and create a new file so that I can diff just to check what has gone on.) – X Tian Jun 24 '15 at 15:47
  • Thank you but that also delete "hotmail" lines that are not in the email field (third??). For example if the last field was hotmail but the email field was not a hotmail address that line would also be deleted. – Teddy291 Jun 24 '15 at 15:55
1

Using csvkit:

csvgrep -c 3 -i -r '(?i)hotmail' file.csv

This assumes file.csv has a header line.

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.