Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What's the simplest way to remove lines from one file matched with lines from another file? For example, if I have the following files:

file1.csv:

u2@domain.com

file2.csv:

1,u1@domain.com,somehash1
2,u2@domain.com,somehash2
3,u3@domain.com,somehash3

As a result I'd like to have file3.csv:

1,u1@domain.com,somehash1
3,u3@domain.com,somehash3

What's the fastest way to solve this task? These files are a few GB in size.

share|improve this question
2  
Seems too large for anything but a coding solution honestly... –  soandos Jul 25 '11 at 23:09
2  
Do you have enough RAM to hold all the files in RAM at the same time + 2 gigs? If not, you will need code, as no program can even hold them open at the same time. –  soandos Jul 25 '11 at 23:25
    
That's a good point. While my solution works in theory, memory is probably going to be a limiting factor. Perhaps you can break the files up first? –  Cam Jackson Jul 26 '11 at 0:03

5 Answers 5

up vote 4 down vote accepted

grep -v -F -f file1.csv file2.csv > file3.csv seems the simplest. But you should do performance tests with smaller files first. (I agree with soandos' comment that such big files might need a dedicated solution.)

share|improve this answer
    
that's how it was solved: cat file2.csv | fgrep -vf file1.csv > file3.csv –  A.B Jul 27 '11 at 6:49
    
Thanks for accepting and good to hear you've solved it! –  jankes Jul 27 '11 at 8:55
2  
Apparently, what you've used is effectively the same method, though represents an example of useless use of cat. You could also use < file2.csv | fgrep -vf file1.csv > file3.csv. –  jankes Jul 27 '11 at 9:04
awk -F, '
  FILENAME == ARGV[1] {to_remove[$1]=1; next}
  ! ($2 in to_remove) {print}
' file1.csv file2.csv > file3.csv

You have to have enough memory to read in file1 at once.

Here's another option: join

$ join -t , -v 2 -1 1 -2 2 file1.csv file2.csv
u1@domain.com,1,somehash1
u3@domain.com,3,somehash3

However, from the man page "Important: FILE1 and FILE2 must be sorted on the join fields." so factor that into your decision.

share|improve this answer

You could loop over each line in file1, and grep matching lines out of file2?

cp file2.csv file3.csv
cat file1.csv | while read line; do
    grep -v ${line%?} file3.csv > temp.csv
    cat temp.csv > file3.csv
done
rm -f temp.csv

Untested.

Edit: Tested, seems to work OK. Just make sure you have a trailing newline in file1.

share|improve this answer
    
The 'line' variable won't have a trailing newline so you don't need to chop off the last char -- that might actually lead to false positives. 'cat temp.csv > file3.csv' is more efficiently written as 'mv temp.csv file3.csv'. But especially, you're processing (reading AND writing) the larger file2 multiple times (once for each line in file1) -- there are approaches that will only pass through file2 once. –  glenn jackman Jul 26 '11 at 0:48
    
Hmm, when I was testing it, it wasn't working initially. I added set -x and set -v and saw that a newline was on the end of the string being grep'd for, so I added the %? and it worked... And you are correct that this processes file2 once for each line in file1, but surely the only other approach would be to read file1 once for each line in file2? The OP said the files were 'few gigs each', so I'm not sure how much difference there is between file sizes. –  Cam Jackson Jul 26 '11 at 1:05

Does file1.csv have to stay unmodifieed?

sed 's|.*|/^&.*/d|' file1.csv > file1.sed
sed -f file1.sed file2.csv > file3.csv 

I don't know how much memory it consumes. AFAIK, it will test the whole -sed file each time on the whole input (2.csv).

If the input is sorted, and the patterns are sorted too, you could implement a faster solution.

share|improve this answer

Make sure file3.csv exists (and it's empty)

echo > file3.csv
diff file1.csv file2.csv | patch file3.csv

Et voilá!

share|improve this answer
    
The lines being removed from file2 are not exact matches of lines from file1. The example in the question shows lines from file1 being a subset of a line from file2, so this won't work will it? –  Cam Jackson Jul 26 '11 at 5:43
    
Right, it will not work. –  mainframezen Jul 27 '11 at 17:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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