Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Field matching between 2 files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Field matching between 2 files
# 1  
Old 08-12-2010
Field matching between 2 files
Okay so I'm pretty new to scripting therefore this problem seems pretty tough. I have a main file that has a column of IP addresses and I have to compare it with 3 separate files that also have IP address columns.

These 3 files are automatically generated from 3 different servers.

Each time an IP address from the main file matches an IP address from one of the 3 server files. It has to add a new field and name it Server1 (if it matched the first file), Server2 etc...

Here's a visual output:

The main file:
Code:
Kesik,1,172.17.214.93,4005
Erisim var,2,172.17.214.93,4006
Erisim var,3,172.17.214.93,4007
Erisim var,4,192.168.13.87,4033
Kesik,5,172.28.251.21,4014
Kesik,6,192.168.13.87,4034

The files generated from the server all have the same type of output:
Code:
AVAE34-IS1472-2P01.1,In Service,AMM 2PB,somsrv01,12713,AVAE34-IS1472-2P01,MTN12713,1,172.17.213.145 
AVE34-KOTT13-6P-01.1,In Service,AMM 6PC,Undefined,13008,AVE34-KOTT13-6P-01,MTN13008,1,172.17.253.121
AVE4A54-SA4642-6P-01.1,Comms Fail,AMM 6PD,somsrv01,12818,AVE4A54-SA4642-6P-01,MTN12818,1,172.17.146.90

The final output should look something like this (Depending on which server's file the IP address is matched):
Code:
Kesik,1,172.17.214.93,4005, Srv1
Erisim var,2,172.17.214.93,4006, Srv1
Erisim var,3,172.17.214.93,4007, Srv1
Erisim var,4,192.168.13.87,4033, Srv2
Kesik,5,172.28.251.21,4014, Srv3
Kesik,6,192.168.13.87,4034, Srv1

There's also another dilemma. The main file may have the same IP address but a different port number (as you can see from the first 3 rows).

I hope I was clear in my explanation, if anything looks confusing please say so Smilie

Thank you guys!
Last edited by Franklin52; 08-12-2010 at 01:12 PM.. Reason: Please use code tags
# 2  
Old 08-12-2010
In a simple way,

Code:
while IFS="," read f1 f2 IP f4
do
        F1=$(grep "$IP" ip1)
        F2=$(grep "$IP" ip2)
        F3=$(grep "$IP" ip3)
        if [ "$F1" ]; then
                echo "$f1,$f2,$IP,$f4,Srv1"
        elif [ "$F2" ]; then
                echo "$f1,$f2,$IP,$f4,Srv2"
        elif [ "$F3" ]; then
                echo "$f1,$f2,$IP,$f4,Srv3"
        else
                echo "$f1,$f2,$IP,$f4"
        fi
done < main_file


ip1, ip2, ip3 are the individual files.
Please note, while searching for the IP in individual files, there is no check whether the IP address is specifically in the last field. it is just searching the IP in the file.
# 3  
Old 08-12-2010
Code:
awk -F, 'NR==FNR{a[NR]=$0;b[NR]=$3;max=NR;next}{c[$9]=FILENAME}END{for (i=1;i<=max;i++){print a[i]", "c[b[i]]}}' main srv1 srv2 srv3

BTW, when you post sample data, please use data that can be used for testing and can produce proper results, cause I spent 5 minutes editing your sample "files" to match proper IPs and creating "srv2" and "srv3".
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to average field if matching string in another

In the awk below I am trying to get the average of the sum of $7 if the string in $4 matches in the line below it. The --- in the desired out is not needed, it is just to illustrate the calculation. The awk executes and produces the current out. I am not sure why the middle line is skipped and the... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. UNIX for Beginners Questions & Answers

Continued trouble matching fields in different files and selective field printing ([g]awk)

I apologize in advance, but I continue to have trouble searching for matches between two files and then printing portions of each to output in awk and would very much appreciate some help. I have data as follows: File1 PS012,002 PRQ 0 1 1 17 1 0 -1 3 2 1 2 -1 ... (7 Replies)
Discussion started by: jvoot
7 Replies

3. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

In the awk below I am trying to set/update the value of $14 in file2 in bold, using the matching NM_ in $12 or $9 in file2 with the NM_ in $2 of file1. The lengths of $9 and $12 can be variable but what is consistent is the start pattern will always be NM_ and the end pattern is always ;... (2 Replies)
Discussion started by: cmccabe
2 Replies

4. Shell Programming and Scripting

Field matching in two data files

Hello, I am looking to output all of the lines from file2 whose 11th field is present in the first field in file1. Then the second field from file1 should be appended as such: file1: 2222 0.35 4444 0.25 5555 0.75 file2: col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 1111 col1 col2... (4 Replies)
Discussion started by: palex
4 Replies

5. Shell Programming and Scripting

Sum column values matching other field

this is part of a KT i am going thru. i am writing a script in bash shell, linux where i have 2 columns where 1st signifies the nth hour like 00, 01, 02...23 and 2nd the file size. sample data attached. Desired output is 3 columns which will give the nth hour, number of entries in nth hour and... (3 Replies)
Discussion started by: alpha_1
3 Replies

6. Shell Programming and Scripting

Matching two files with special field separator

Hello, I have a file with such structure: >ENSGALG00000000011|ENSGALT00000000012|57|1123|1125 AACTGTGTGTTTTTT >ENSGALG00000000012|ENSGALT00000000013|57|1145|1155 AAAAAAGGTCCTGTGTGC >ENSGALG00000000015|ENSGALT00000000014|57|1144|1155 AAAATGTGTGTGTGTGTGTGTG I want to use another file... (8 Replies)
Discussion started by: Homa
8 Replies

7. Shell Programming and Scripting

Adding matching field

I have a flat file test.log red,5,,,,, green,7,,,,, blue,4,,,,, red,8,,,,, green,9,,,,, How i get a a result: blue,4,,,,, green,16,,,,, red,13,,,,, Thanks Video tutorial on how to use code tags in The UNIX and Linux Forums. (2 Replies)
Discussion started by: sabercats
2 Replies

8. Shell Programming and Scripting

Matching lines across multiple csv files and merging a particular field

I have about 20 CSV's that all look like this: "","","","","","","","","","","","","","","",""What I've been told I need to produce is the exact same thing, but with each file now containing the start_code from every other file where the email matches. It doesn't matter if any of the other... (1 Reply)
Discussion started by: Demosthenes
1 Replies

9. Shell Programming and Scripting

Need some help matching a field in one file with a field in another

One file (file1) is as such: Abc.txt 1.1 1.3 Abc_v2.txt 1.1 1.4 Tree.txt 1.3 1.4 Grass.txt 1.3 4.5 The other (file2): Horse.txt 1.1 ref23232 Abc.txt 1.1 1.2 ref 3232-3232 1.3 ref 3232-3232 Plane.txt 1.1 1.2 ref 3232-3232 1.3 ref 3232-3232 1.4 Tree.txt 1.3 ref 3232-3232 1.4... (5 Replies)
Discussion started by: linuxkid
5 Replies

10. Shell Programming and Scripting

Print matching field using awk

Hi All, I have a string like below: str="Hold=True Map=False 'This will map the data' Run=Yes Modify=False" I want to print the field Run=Yes and retrive the value "Yes". I cannot use simple awk command because the position of the "Run" will be different at different times. Is there a way... (6 Replies)
Discussion started by: deepakgang
6 Replies
Login or Register to Ask a Question