Remove rows from file2 if it exists in file1


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Remove rows from file2 if it exists in file1
# 1  
Old 05-08-2014
Remove rows from file2 if it exists in file1

I have 2 file, file1 and file2. file1 has some keys and file2 has keys+some other data. I want to remove the lines from file2,if the key for that line exists in file1.

file1:
Code:
key1
key2

flie2:
Code:
key1,moredata
key2,moredata
key3,moredata

Required output:
Code:
key3,moredata

Thanks

EDIT:
What I have tried:
Code:
awk 'NR==FNR{A[$1];next} {B[$1];next} END {for ( k in B) {if ( ! ( k in A)) { print k}}}' file1 file2

But this just gives the every keys in file2.
# 2  
Old 05-08-2014
try:
Code:
awk 'NR==FNR {a[$0]=$0; next} !a[$1]' file1 FS=, file2

# 3  
Old 05-08-2014
Try
Code:
grep -vffile1 file2

# 4  
Old 05-08-2014
Quote:
Originally Posted by rdrtx1
try:
Code:
awk 'NR==FNR {a[$0]=$0; next} !a[$1]' file1 FS=, file2

Not knowing what the keys are like, it is a mistake to use !a[$1]. What if one of the keys is 00000? The check should be done using the 'in' operator. Or, instead of storing $0, simply increment the array member so its value is positive or undefined.

Regards,
Alister

---------- Post updated at 11:09 AM ---------- Previous update was at 10:40 AM ----------

Quote:
Originally Posted by RudiC
Try
Code:
grep -vffile1 file2

I don't think that's a good solution. If the key can appear as part of the data, there can be false positive matches which incorrectly exclude a line. Another possible issue could be regular expression metacharacters in the key.

Regards,
Alister
# 5  
Old 05-08-2014
Code:
$ awk -F, 'FNR==NR{A[$1];next}!($1 in A)' file1 file2
key3,moredata

# 6  
Old 05-08-2014
Combining rdrxt1's suggestion with alister's remark and akshay's suggestion, an alternate version would be:
Code:
awk 'FNR==NR{A[$1]; next}!($1 in A)' file1 FS=, file2

Which would be insensitive to spurious leading/trailing spaces in file1 . This may seem picky, but file1 is more likely to be a file that is edited manually, so space here or there is easily added by accident..
# 7  
Old 05-08-2014
Actually there are leading spaces in file1, when I checked it again. But I am generating file1 using a script, so I can easily remove them.

EDIT:
This seems to be working for me:
Code:
awk 'NR==FNR{A[$1]=$1;next} !($1 in A)' file1 FS=, file2

and this is also working, even though I have leading whitespaces in file1.
Code:
awk 'NR==FNR{A[$1];next} !($1 in A)' file1 FS=, file2

Thanks everyone for you help Smilie

Last edited by chacko193; 05-08-2014 at 02:48 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk- Indexing a list of numbers in file2 to print certain rows in file1

Hi Does anyone know of an efficient way to index a column of data in file2 to print the coresponding row in file1 which corresponds to the data in file2 AND 30 rows preceding and after the row in file1. For example suppose you have a list of numbers in file2 (single column) as follows:... (6 Replies)
Discussion started by: Geneanalyst
6 Replies

2. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

3. UNIX for Dummies Questions & Answers

Compare file1 and file2, print matching lines in same order as file1

I want to print only the lines in file2 that match file1, in the same order as they appear in file 1 file1 file2 desired output: I'm getting the lines to match awk 'FNR==NR {a++}; FNR!=NR && a' file1 file2 but they are in sorted order, which is not what I want: Can anyone... (4 Replies)
Discussion started by: pathunkathunk
4 Replies

4. Shell Programming and Scripting

If file1 and file2 exist then

HI, I would like a little help on writing a if statement. What i have so far is: #!/bin/bash FILE1=path/to/file1 FILE2=path/to/file2 echo ${FILE1} ${FILE2} if ] then echo file1 and file2 not found else echo FILE ok fi (6 Replies)
Discussion started by: techy1
6 Replies

5. Shell Programming and Scripting

look for line from FILE1 at FILE2

Hi guys! I'm trying to write something to find each line of file1 into file2, if line is found return YES, if not found return NO. The result can be written to a new file. Can you please help me out? FILE1 INPUT: WATER CAR SNAKE (in reality this file has about 600 lines each with a... (2 Replies)
Discussion started by: demmel
2 Replies

6. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

7. Shell Programming and Scripting

Remove words from file2 that don't exist in file1

Hi I have to list of words file1 and file2, I want to compare both lists and remove from file2 all the words that don't exist in file1. How can I do this? Many thanks (4 Replies)
Discussion started by: noliveira
4 Replies

8. Shell Programming and Scripting

Remove lines in file1 with values from file2

Hello, I have two data files: file1 12345 aa bbb cccc 98765 qq www uuuu 76543 pp rrr bbbbb 34567 nn ccc sssss 87654 qq ppp rrrrr file2 98765 34567 I need to remove the lines from file1 if the first field contains a value that appears in file2: output 12345 aa bbb cccc 76543 pp... (2 Replies)
Discussion started by: palex
2 Replies

9. Shell Programming and Scripting

grep -f file1 file2

Hi I started to learn bash a week ago. I need filter the strings from the last column of a "file2" that match with a column from an other "file1" file1: chr10100036394-100038350AK077761 chr10100041065-100046547AK032226 chr10100041065-100046547AK016270 chr10100041065-100046547AK078231 ...... (6 Replies)
Discussion started by: geparada88
6 Replies

10. Shell Programming and Scripting

grep -f file1 file2

Wat does this command do? fileA is a subset of fileB..now, i need to find the lines in fileB that are not in fileA...i.e fileA - fileB. diff fileA fileB gives the ouput but the format looks no good.... I just need the contents alone not the line num etc. (7 Replies)
Discussion started by: vijay_0209
7 Replies
Login or Register to Ask a Question