Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Comparing two columns in two files and printing a third based on a match

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparing two columns in two files and printing a third based on a match
# 8  
Old 09-18-2018
strange...
given your sample files:
Code:
  awk -F, 'FNR==NR{f2[$1]=$2;next} FNR==1{print $0, "VLAN Name";next} {print $0,($5 in f2)?f2[$5]:"NA"}' OFS=, file2 file1

produces...
Code:
Hostname,Port,Name,Status,VLAN,Duplex,Speed,Type,VLAN Name
switch1,gi2/1,trunk,active,1,a-100,full,10g,10GBase-CU 3M,default
switch1,gi3/1,link to data closet,disabled,2,half,a-1000,No XCVR,voice

# 9  
Old 09-18-2018
Update, I have been making a little progress but still dont have it. This will print fields when there is a match but it isnt printing fields from file1. It will print all lines, but none of the matching info. Any help (explanation would be awesome too) is greatly appreciated!!
Code:
$  awk -F',' 'FNR==NR{a[$1]=$2;next}{if(a[$5]==""){a[$5]=0}; print $1,$2,$5,a[$1,$2]}' file1 file2

------ Post updated at 12:55 AM ------

Below is a sample of what is being returned

Code:
$  awk -F',' 'FNR==NR{a[$1]=$2;next}{if(a[$5]==""){a[$5]=0}; print $1,$2,$5,a[$1,$2]}' file1 file2
Hostname Port Vlan
onshintstat Te1/1           trunk
onshintstat Te1/2           trunk
onshintstat Te1/3           trunk
onshintstat Te1/4           trunk
onshintstat Te1/5           572
onshintstat Te1/6           572

Last edited by Scrutinizer; 09-18-2018 at 11:43 PM.. Reason: code tags
# 10  
Old 09-18-2018
Quote:
Originally Posted by dis0wned
Update, I have been making a little progress but still dont have it. This will print fields when there is a match but it isnt printing fields from file1. It will print all lines, but none of the matching info. Any help (explanation would be awesome too) is greatly appreciated!!
Code:
$  awk -F',' 'FNR==NR{a[$1]=$2;next}{if(a[$5]==""){a[$5]=0}; print $1,$2,$5,a[$1,$2]}' file1 file2

------ Post updated at 12:55 AM ------

Below is a sample of what is being returned

Code:
$  awk -F',' 'FNR==NR{a[$1]=$2;next}{if(a[$5]==""){a[$5]=0}; print $1,$2,$5,a[$1,$2]}' file1 file2

Hostname Port Vlan
onshintstat Te1/1 trunk
onshintstat Te1/2 trunk
onshintstat Te1/3 trunk
onshintstat Te1/4 trunk
onshintstat Te1/5 572
onshintstat Te1/6 572
your array a is populated with the single dimension. Why are you trying to print it indexing by 2 indecies?
Have you tried the last proposed solution from post #8?
# 11  
Old 09-19-2018
I pulled the csv into excel and sanitized it, Below are snippets of the actual file:



FILE1:
Code:
Hostname	Port	Name	Status	Vlan	Duplex	Speed	Type
Switch	Te1/1     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/2     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/3     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/4     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/5     	link description	"	connected    "	"	572        "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/6     	link description	"	connected    "	"	572        "	"	  full "	"	   10G "	"	10GBase-CU 3M"
Switch	Te1/7     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te1/8     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te1/9     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te1/10    	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te1/11    	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	10GBase-SR"
Switch	Te1/12    	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/1     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/2     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te2/3     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/4     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te2/5     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te2/6     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/7     	"	                   "	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	10GBase-SR"
Switch	Te2/8     	link description	"	notconnect   "	"	1          "	"	  full "	"	  auto "	"	No XCVR"
Switch	Te2/9     	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/10    	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"
Switch	Te2/11    	link description	"	connected    "	"	trunk      "	"	  full "	"	   10G "	"	10GBase-SR"

FILE2:
Code:
VLAN	Name	Status	Ports	Device
1	default	active	Te1-7 Te1-8 Te1-10 Te1-11 Te2-2 Te2-4 Te2-5 Te2-7 Te2-8 Te3-2 Te3-3 Te3-4 Te4-2 Te4-3 Te4-4 Gi5-8 Gi5-9 Gi5-10 Gi5-11 Gi5-14 Gi5-15 Gi5-16 Gi5-18 Gi5-19 Gi5-20 Gi5-22 Gi5-23 Gi5-25 Gi5-26 Gi5-27 Gi5-28 Gi5-29 Gi5-30 Gi5-31 Gi5-32 Gi5-33 Gi5-34 Gi5-35 Gi5-36 Gi5-37 Gi5-38 Gi5-39 Gi5-40 Gi5-41 Gi5-42 Gi5-43 Gi5-44 Gi5-45 Gi5-46 Gi6-3 Gi6-4 Gi6-5 Gi6-6 Gi6-7 Gi6-8 Gi6-9 Gi6-10 Gi6-11 Gi6-12 Gi6-13 Gi6-14 Gi6-15 Gi6-16 Gi6-17 Gi6-18 Gi6-19 Gi6-20 Gi6-26 Gi6-27 Gi6-28 Gi6-30 Gi6-32 Gi6-33 Gi6-34 Gi6-35 Gi6-36 Gi6-38 Gi6-39 Gi6-40 Gi6-47 Gi7-1 Gi7-16 Gi7-17 Gi7-19 Gi7-20 Gi7-21 Gi7-23 Gi7-24 Gi7-28 Gi7-29 Gi7-30 Gi7-40	./Core.logshvlan.txt
10	stuff1	active	Gi5-12 Gi7-8 Gi7-10 Gi7-11 Gi7-12	./Core.logshvlan.txt
11	stuff2	active		./Core.logshvlan.txt
16	stuff3	active		./Core.logshvlan.txt
17	stuff4	active		./Core.logshvlan.txt
19	stuff5	active		./Core.logshvlan.txt
20	stuff6	active		./Core.logshvlan.txt
25	stuff7	active	Gi5-13 Gi5-21 Gi6-29 Gi6-44 Gi6-46 Gi6-48 Gi7-14 Gi7-15	./Core.logshvlan.txt
30	stuff8	active		./Core.logshvlan.txt
32	stuff9	active		./Core.logshvlan.txt
40	stuff10	active		./Core.logshvlan.txt
50	stuff11	active		./Core.logshvlan.txt
51	stuff12	active		./Core.logshvlan.txt
52	stuff13	active		./Core.logshvlan.txt
60	stuff14	active		./Core.logshvlan.txt
100	stuff15	active		./Core.logshvlan.txt
172	stuff16	active		./Core.logshvlan.txt



Output when I run the command:
Code:
NAonshintstat	Gi7/28    	                        	    disabled     	1	      auto 	          auto 	        10/100/1000-TX
NAonshintstat	Gi7/29    	                        	    disabled     	1	      auto 	          auto 	        10/100/1000-TX
NAonshintstat	Gi7/30    	                        	    disabled     	1	      auto 	          auto 	        10/100/1000-TX
NAonshintstat	Gi7/31    	Sanitized	    connected    	525	    a-full 	        a-1000 	        10/100/1000-TX
NAonshintstat	Gi7/32    	Sanitized	    notconnect   	525	      auto 	          auto 	        10/100/1000-TX
NAonshintstat	Gi7/33    	Sanitized	    connected    	998	    a-full 	        a-1000 	        10/100/1000-TX
NAonshintstat	Gi7/34    	Sanitized	    connected    	998	    a-full 	        a-1000 	        10/100/1000-TX
NAonshintstat	Gi7/35    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/36    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/37    	Sanitized	    connected    	505	    a-half 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/38    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/39    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/40    	                        	    disabled     	1	      auto 	          auto 	        10/100/1000-TX
NAonshintstat	Gi7/41    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/42    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/43    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/44    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/45    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/46    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/47    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX
NAonshintstat	Gi7/48    	Sanitized	    connected    	505	    a-full 	         a-100 	        10/100/1000-TX



What I am seeing is that it did not make any matches (there are matches even though the snipped of file1 does not reflect it)

------ Post updated at 01:50 PM ------

This was the output using the command that you supplied, it appended NA at the beginning of every line and made 0 matches
# 12  
Old 09-19-2018
Did you check your file for DOS line terminators (<CR> = ^M = 0x0D)?
This User Gave Thanks to RudiC For This Post:
dis0wned
# 13  
Old 09-19-2018
And what command did you use that produced the output you showed us in post #11?

In addition to what RudiC has already said, we also have to assume that you realize that the sanitized output you showed us from excel has lost all field boundaries. So, any awk script that you might try to use with that sanitized output as an input file is hopelessly incapable of determining which text belongs in which field.
This User Gave Thanks to Don Cragun For This Post:
dis0wned
# 14  
Old 09-19-2018
Please see below: I removed the dos carriage returns / line feeds and ran the command provided again.


Code:
TGY9 ~/testfiles/vlanint
$ tr -d '\015' <file2 >file22

TGY9 ~/testfiles/vlanint
$ awk 'FNR==NR{f2[$1]=$5;next} FNR==1{print $0, "VLAN Name";next} {print $0,($2 in f2)?f2[$2]:"NA"}' OFS=, file2 file1
Hostname,Port,Name,Status,Vlan,Duplex,Speed,Type,VLAN Name
onshintstat,Te1/1     ,     Trunk-to-    ,    connected    ,  trunk      ,      full ,           10G ,        10GBase-CU 3M,,NA
onshintstat,Te1/2     ,     Trunk-to-    ,    connected    ,  trunk      ,      full ,           10G ,        10GBase-CU 3M,,NA
onshintstat,Te1/3     ,     Trunk-to-   ,    connected    ,  trunk      ,      full ,           10G ,        10GBase-CU 3M,,NA
onshintstat,Te1/4     ,     Trunk-to-   ,    connected    ,  trunk      ,      full ,           10G ,        10GBase-CU 3M,,NA
onshintstat,Te1/5     ,     nexu  ,    connected    ,  572        ,      full ,           10G ,        10GBase-CU 3M,,NA
onshintstat,Te1/6     ,     nexu  ,    connected    ,  572        ,      full ,           10G ,        10GBase-CU 3M,,NA

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, AND code segments.
Last edited by Don Cragun; 09-20-2018 at 04:47 PM.. Reason: Add CODE tags again.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

awk pattern match not printing desired columns

Hi all, I'm trying to match the following two files with the code below: awk -F, 'NR==FNR {a=$0; next} ($12,$4) in a {print $12,$1,a}' OFS="," file4.csv file3.csv but the code does not print the entire row from file4 in addition to column 12 and 1 of file3. file4: o,c,q,co,ov,b... (1 Reply)
Discussion started by: bkane3
1 Replies

3. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

4. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

5. UNIX for Dummies Questions & Answers

Comparing two test files and printing out the values that do not match

Hi, I have two text files with matching first columns. Some of the values in the second column do not match. I want to write a script to print out the rows (only the first column) where the values in the second column do not match. Example: Input 1 A 1 B 2 C 3 D 4 Input 2 A 2 B 2... (6 Replies)
Discussion started by: evelibertine
6 Replies

6. UNIX for Dummies Questions & Answers

Comparing two text files by a column and printing values that do not match

I have two text files where the first three columns are exactly the same. I want to compare the fourth column of the text files and if the values are different, print that row into a new output file. How do I go about doing that? File 1: 100 rs3794811 0.01 0.3434 100 rs8066551 0.01... (8 Replies)
Discussion started by: evelibertine
8 Replies

7. UNIX for Dummies Questions & Answers

Comparing the 2nd column in two different files and printing corresponding 9th columns in new file

Dear Gurus, I am very new to UNIX. I appreciate your help to manage my files. I have 16 files with equal number of columns in it. Each file has 9 columns separated by space. I need to compare the values in the second column of first file and obtain the corresponding value in the 9th column... (12 Replies)
Discussion started by: Unilearn
12 Replies

8. Shell Programming and Scripting

Comparing two files and printing 2nd column if match found

Hi guys, I'm rather new at using UNIX based systems, and when it comes to scripting etc I'm even newer. I have two files which i need to compare. file1: (some random ID's) 451245 451288 136588 784522 file2: (random ID's + e-mail assigned to ID) 123888 xc@xc.com 451245 ... (21 Replies)
Discussion started by: spirm8
21 Replies

9. Shell Programming and Scripting

printing words based on column match

pls help Input: file1 word1 text1 word2 text2 word3 text3 file2 word1 text11 word3 text13 can u pls help in getting the same output: file1 text1 text2 text3 (1 Reply)
Discussion started by: bha148
1 Replies

10. Shell Programming and Scripting

Comparing Columns and printing the difference from a particular file

Gurus, I have one file which is having multiple columns and also this file is not always contain the exact columns; sometimes it contains 5 columns or 12 columns. Now, I need to find the difference from that particular file. Here is the sample file: param1 | 10 | 20 | 30 | param2 | 10 |... (6 Replies)
Discussion started by: buzzusa
6 Replies
Login or Register to Ask a Question