Input file needs to match a column and print the entire line


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Input file needs to match a column and print the entire line
# 1  
Old 06-15-2016
Input file needs to match a column and print the entire line

I have a file with class c IP addresses that I need to match to a column and print the matching lines of another file.

I started playing with grep -if file01.out file02.out but I am stuck as to how to match it to a column and print the matching lines;

Code:
cat file01.out

10.150.140
192.168.30
192.168.40
192.168.50
192.168.60

cat file02.out

192.168.200.10,192.168.40.44,22
192.168.100.11,192.168.249.255,23
192.168.118.12,192.168.30.200,22
10.67.160.295,192.168.248.31,53
10.68.132.20,192.168.60.33,443

The result needs to look like this;

Code:
192.168.200.10,192.168.40.44,22
192.168.118.12,192.168.30.200,22
10.68.132.20,192.168.60.33,443

Please explain what your command, code does. I am learning Smilie

Last edited by lewk; 06-15-2016 at 05:02 PM..
# 2  
Old 06-15-2016
In you sample file02.out sample, the 3rd line is:
Code:
192.168.118.12,192.192.168.30.200,22

Is that correct, or did you intend to have:
Code:
192.168.118.12,192.168.30.200,22

Are you looking for a match on any substring of the 2nd field in file02.out, or are you looking for an exact match on the first three components of the IP address in the 2nd field in file02.out?

What operating system and shell are you using?
# 3  
Old 06-15-2016
Quote:
Originally Posted by Don Cragun
In you sample file02.out sample, the 3rd line is:
Code:
192.168.118.12,192.192.168.30.200,22

Is that correct, or did you intend to have:
Code:
192.168.118.12,192.168.30.200,22

Are you looking for a match on any substring of the 2nd field in file02.out, or are you looking for an exact match on the first three components of the IP address in the 2nd field in file02.out?

What operating system and shell are you using?
Thanks for spotting that error, I corrected it. I intended to have as per your example. I am looking for an exact match on the first three components on the second field.

I am using Ubuntu but right now I am on Cygwin Smilie
# 4  
Old 06-15-2016
A fairly simple way to do this with awk is:
Code:
awk -F, ' 		# Invoke awk to process your files with the field
			# separator set to ",".
FNR == NR {		# For lines in the 1st input file (the # of records in
			# the current file is the same as the # of records in
			# all files read so far)...
	ip[$1 "."]	# Add an element to the array ip[] with the index being
			# the text found in the 1st field from the first file
			# with a "." added to the end of that string.
	next		# Stop processing this input record and read the next
			# input line.
}
{	for(i in ip)	# For lines read from any following input files, loop
			# through all of the index values that have been used in
			# the array ip[], with "i" set to a different index each
			# time through the loop...
		if(index($2, i) == 1) {	# if the index for this time through the
					# loop identically matches a substring
					# starting with the 1st character of the
					# 2nd field in this file...
			print	# print the current input line...
			next	# and stop processing this line and read the
				# next input line
		}
}' file0[12].out	# End the script and name the two input files.

If someone else wants to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.

Note that I used index() rather than using an ERE match because the period is a special character in an ERE that matches any character (not just a period), so setting up the index when adding elements to the ip[] array would have been more difficult:
Code:
	ip[$1 "."]

would need to be something more like:
Code:
	gsub(/[.]|$/, "[.]", $1)
	ip["^" $1]

but the match in the loop:
Code:
		if(index($2, i) == 1) {

would have been simpler (but would probably also run slower):
Code:
		if($2 ~ i) {

This User Gave Thanks to Don Cragun For This Post:
# 5  
Old 06-16-2016
brilliant, thank you for the detailed explanation too!

---------- Post updated at 10:46 PM ---------- Previous update was at 05:25 PM ----------

How would I modify it if for example I do not want to show lines where a match was found in column 2?

Code:
cat file01.out

10.150.140
192.168.30
192.168.40
192.168.50
192.168.60

cat file02.out

192.168.200.10,192.168.40.44,22
192.168.100.11,192.168.249.255,23
192.168.118.12,192.168.30.200,22
10.67.160.295,192.168.248.31,53
10.68.132.20,192.168.60.33,443

The result needs to look like this;

Code:

192.168.100.11,192.168.249.255,23
10.67.160.295,192.168.248.31,53

# 6  
Old 06-16-2016
Move the print statement as shown below. Current script:
Code:
awk -F, '
FNR == NR {
	ip[$1 "."]
	next
}
{	for(i in ip)
		if(index($2, i) == 1) {
			print
			next
		}
}' file0[12].out

Script to print lines with no IP matches instead of lines with a matching IP:
Code:
awk -F, '
FNR == NR {
	ip[$1 "."]
	next
}
{	for(i in ip)
		if(index($2, i) == 1) {
			next
		}
	print
}' file0[12].out

This User Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. UNIX for Beginners Questions & Answers

Use strings from nth field from one file to match strings in entire line in another file, awk

I cannot seem to get what should be a simple awk one-liner to work correctly and cannot figure out why. I would like to use patterns from a specific field in one file as regex to search for matching strings in the entire line ($0) of another file. I would like to output the lines of File2 which... (1 Reply)
Discussion started by: jvoot
1 Replies

3. Shell Programming and Scripting

Print next line beside preceding line on column match

Hi, I have some data like below: John 254 Chris 254 Matt 123 Abe 123 Raj 487 Moh 487 How can i print it using awk to have: 254 John,Chris 123 Matt,Abe 487 Raj,Moh Thanks. (4 Replies)
Discussion started by: james2009
4 Replies

4. Shell Programming and Scripting

Remove entire line from a file if 1st column matches a pattern

I have one requirement to delete all lines from a file if it matches below scenario. File contains three column. Employee Number, Employee Name and Employee ID Scenario is: delete all line if Employee Number (1st column) contains below 1. Non-numeric Employee Number 2. Employee Number that... (3 Replies)
Discussion started by: anshu ranjan
3 Replies

5. Shell Programming and Scripting

How to print the entire line if the mentioned match is found?

Hello Everyone, I have a file with 5 fields in each line just like mentioned below. Also the 4th field is time elapsed(hh:mm:ss) since the process is running xyz abc status 23:00:00 idle abc def status 24:00:00 idle def gji status 27:00:02 idle fgh gty status 00:00:00 idle Here I... (8 Replies)
Discussion started by: rahul2662
8 Replies

6. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

7. Shell Programming and Scripting

print when column match with other file

Hello all, please help. There are two file like this: file1: 1197510.0 294777.7 9666973.0 21.6 1839.8 1197510.0 294777.7 9666973.0 413.2 2075.9 1197510.0 294777.7 9666973.0 689.3 2260.0 ... (1 Reply)
Discussion started by: attila
1 Replies

8. Shell Programming and Scripting

Match a line in File 1 with Column in File 2 and print whole line in file 2 when matched

Hi Experts, I am very new to scripting and have a prb since few days and it is urgent to solve so much appreciated if u help me. i have 2 files file1.txt 9647810043118 9647810043126 9647810043155 9647810043161 9647810043166 9647810043185 9647810043200 9647810043203 9647810043250... (22 Replies)
Discussion started by: mustafa.abdulsa
22 Replies

9. Shell Programming and Scripting

Print entire line based on value in a column

Friends, File1.txt abc|0|xyz 123|129|opq def|0|678 890|pqw|sdf How do I print the entire line where second column has value is 0? Expected Result: abc|0|xyz def|0|678 Thanks, Prashant ---------- Post updated at 02:14 PM ---------- Previous update was at 02:06 PM ---------- ... (1 Reply)
Discussion started by: ppat7046
1 Replies

10. Shell Programming and Scripting

Awk+Grep Input file needs to match a column and print the entire line

I'm having problems since few days ago, and i'm not able to make it works with a simple awk+grep script (or other way to do this). For example, i have a input file1.txt: cat inputfile1.txt 218299910417 1172051195 1172070231 1172073514 1183135117 1183135118 1183135119 1281440202 ... (3 Replies)
Discussion started by: poliver
3 Replies
Login or Register to Ask a Question

Featured Tech Videos