Comparing fields in 1 file to another file

Shell Programming and Scripting
1

Need help with doing field comparisons.

File1
204.11.23.1 fastmovie.mp4
209.13.1.1 slowmovie.mp4

file 2
NY USA 201.1.1.1 200 freemovie.mp4
CA USA 204.11.23.1 404 notfastmovie.mp4
CA USA 204.11.23.1 200 fastmovie.mp4

basically need to take the first file and find exact matches in field 3 and 5 of the second file and I would print the entire line from file 2. So the output here would only be line 3 of file 2

2

Hi,

try:

awk 'NR==FNR{a[$1$2]}{if ($3$5 in a) print $0}' file1 file2

gives me:

CA USA 204.11.23.1 200 fastmovie.mp4

HTH Chris

3 
awk 'NR==FNR{a[$1]=$2;next}($5==a[$3])' File1 File2
4

Ok a couple things. If file 2 is huge on the magnitude of over 6 gig is awk the best way to handle it? finally in file 2 the filename field wouldn't be an exact match in file 2 it would actually be listed like /home/httpd/www/fastmovie.mp4

5

I have a similar probelm to the OP but need to compare jsut the 1st fields of the 2 files and print out the whole line as he needs. However, I thought i'd try his problem and work from it, but your solution only seems to print out the whole of file 1...could you re-look into this please?

---------- Post updated at 11:01 AM ---------- Previous update was at 10:59 AM ----------

As I stated I have a similar problem, but tried to work through this example to solve mine (I want to compare jsut the 1st field of each file), but your code seems to give a syntax error, although I have it down exactly the same. Any idea's?

6 
$ cat File1
204.11.23.1 fastmovie.mp4
209.13.1.1 slowmovie.mp4

$ Cat File2
NY USA 201.1.1.1 200 freemovie.mp4
CA USA 204.11.23.1 404 notfastmovie.mp4
CA USA 204.11.23.1 200 fastmovie.mp4

$ awk 'NR==FNR{a[$1]=$2;next}($5==a[$3])' File1 File2
CA USA 204.11.23.1 200 fastmovie.mp4

If on Solaris, use /usr/bin/nawk or /usr/xpg4/bin/awk

7

What would I need to do to be able to compare just the 1st fields in the 2 files? The whole equals signs confuse me slightly. Many thanks

8

If you only compare 1st field, then your output should be two lines.

CA USA 204.11.23.1 404 notfastmovie.mp4
CA USA 204.11.23.1 200 fastmovie.mp4

But your expect is only one line, then we will think 5th field also need be compared.

Here is the code which only compare 1st field in File1

$ awk 'NR==FNR{a[$1]=1;next} a[$3] {print}' File1 File2
CA USA 204.11.23.1 404 notfastmovie.mp4
CA USA 204.11.23.1 200 fastmovie.mp4
9

I'm sorry but it jsut doesn't work, even the last code just printed out the whole file again for some reason. I have a slightly different file to the OP, however the layout is exactly the same. I'm doing bash scripting on unix if that makes any difference at all. I don't know why it doesn't work and its really frustrating me!!!!!!!!!!!!

---------- Post updated at 04:14 PM ---------- Previous update was at 11:34 AM ----------

I am on Solaris! SORTED! thanks mate! only took me a day!

10

So my issue with the file being too large was easily solved by just splitting it into 6 files. The 1 field match example works but I need the 1st field to be an exact match and I need the 2nd field to be contained within the 5th field of file 2. So here are the original examples revised to correctly reflect full path being in 5th field of file 2.

File1
204.11.23.1 fastmovie.mp4
209.13.1.1 slowmovie.mp4

file 2
NY USA 201.1.1.1 200 /home/httpd/www/freemovie.mp4
CA USA 204.11.23.1 404 /home/httpd/www/notfastmovie.mp4
CA USA 204.11.23.1 200 /home/httpd/www/fastmovie.mp4

11

Try:

awk -F"[ /]" 'NR==FNR{a[$2];next} $NF in a' File1 File2