awk help: Match data fields from 2 files & output results from both into 1 file | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

awk help: Match data fields from 2 files & output results from both into 1 file

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 12-10-2012
ambroze ambroze is offline
Registered User
 
Join Date: May 2008
Last Activity: 3 October 2013, 1:51 PM EDT
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
awk help: Match data fields from 2 files & output results from both into 1 file

I need to take 2 input files and create 1 output based on matches from each file. I am looking to match field #1 in both files (Userid) and create an output file that will be a combination of fields from
both file1 and file2 if there are any differences in the fields 2,3,4,5,or 6.

Below is an example of where in file2 the First Name=John, Email=john.doe@yahoo.com, and Phone Number=111-222-3333.
Since these fields are different than the values in file1 I need the output in file3 based on the example below using awk. Any help would be greatly appreciated.

file1:

Code:
jdoe|Doe|Johnny|111-222-9999|jdoe@gmail.com|Main Office|1020-771-AHSDEV100|512
1|Userid
2|Last Name
3|First Name
4|Phone Number
5|Email
6|Office
7|Description
8|Account

file2:

Code:
jdoe|Doe|John|111-222-3333|john.doe@yahoo.com|Main Office|0xF48F9F97AB1E9242A6CCB96BA1DB5C79|0x820A4CE019D51F45B7B911CDCDB5208D|Data1|Data2|Data3|Data4|Data5|Data6|0
1|Userid
2|Last Name
3|First Name
4|Phone Number
5|Email
6|Office
7|Office Location
8|Contact UUID
9|Data1
10|Data2
11|Data3
12|Data4
13|Data5
14|Data6
15|Active


file3:

Code:
{ "820A4CE019D51F45B7B911CDCDB5208D", "Doe", "Johnny", "111-222-9999", "jdoe@gmail.com", "jdoe", "F48F9F97AB1E9242A6CCB96BA1DB5C79", "Data1", "0" }

{ 
"file2 field #8 (Office Location) cut -c3-34"
"file1 field #2 (Last Name)"
"file1 field #3 (First Name)"
"file1 field #4 (Phone Number)"
"file1 field #5 (Email)"
"file1 field #1 (Userid)"
"file2 field #7 (Office Location) cut -c3-34"
"file2 field #9 (Data1)
"file2 field #15 (Active)"

Moderator's Comments:
Please use code tags when posting data and code samples!

Last edited by vgersh99; 12-10-2012 at 06:08 PM.. Reason: code tags, please!
Sponsored Links
    #2  
Old 12-10-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts
try:

Code:
awk -F"|" '
NR==FNR {a[$1]=$0; for (i=2; i<=6; i++) b[$1,$i]=b[$1,i]=$i; next}
a[$1] {
  s=0;
  for (i=2; i<=6; i++) {if (!b[$1,$i]) s=1; continue;};
  if (s==1) {
    printf "{ \"";
    printf substr($8,3) "\", \"";
    printf b[$1,2]      "\", \"";
    printf b[$1,3]      "\", \"";
    printf b[$1,4]      "\", \"";
    printf b[$1,5]      "\", \"";
    printf $1           "\", \"";
    printf substr($7,3) "\", \"";
    printf $9           "\", \"";
    printf $15;
    print  "\" }";
  }
}
' file1 file2 > file3

Using only line 1 in example for file1 and file2.
The Following User Says Thank You to rdrtx1 For This Useful Post:
ambroze (12-11-2012)
Sponsored Links
    #3  
Old 12-11-2012
ambroze ambroze is offline
Registered User
 
Join Date: May 2008
Last Activity: 3 October 2013, 1:51 PM EDT
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by rdrtx1 View Post
try:

Code:
awk -F"|" '
NR==FNR {a[$1]=$0; for (i=2; i<=6; i++) b[$1,$i]=b[$1,i]=$i; next}
a[$1] {
  s=0;
  for (i=2; i<=6; i++) {if (!b[$1,$i]) s=1; continue;};
  if (s==1) {
    printf "{ \"";
    printf substr($8,3) "\", \"";
    printf b[$1,2]      "\", \"";
    printf b[$1,3]      "\", \"";
    printf b[$1,4]      "\", \"";
    printf b[$1,5]      "\", \"";
    printf $1           "\", \"";
    printf substr($7,3) "\", \"";
    printf $9           "\", \"";
    printf $15;
    print  "\" }";
  }
}
' file1 file2 > file3

Using only line 1 in example for file1 and file2.
rdrtx1 - This works perfect and is exactly what I was looking for. I did find somethings that I would like to see if they could be added and it's my fault for thinking of them after the fact.

Is there a way to only output into file3 the results where there were differences? If fields 2,3,4,5,or 6 are the same they do not need to be outputted into file3.
    #4  
Old 12-11-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts
try fixed:

Code:
awk -F"|" 'NR==FNR {a[$1]=$0; for (i=2; i<=6; i++) b[$1,$i]=b[$1,i]=$i; next}
a[$1] {  
  s=0; 
  for (i=2; i<=6; i++) {if (!b[$1,$i]) s=1};  
  if (s==1) {    
    printf "{ \"";    
    printf substr($8,3) "\", \"";    
    printf b[$1,2]      "\", \"";    
    printf b[$1,3]      "\", \"";    
    printf b[$1,4]      "\", \"";    
    printf b[$1,5]      "\", \"";    
    printf $1           "\", \"";    
    printf substr($7,3) "\", \"";    
    printf $9           "\", \"";    
    printf $15;    
    print  "\" }";  
  }
}' file1 file2 > file3

The Following User Says Thank You to rdrtx1 For This Useful Post:
ambroze (12-11-2012)
Sponsored Links
    #5  
Old 12-11-2012
ambroze ambroze is offline
Registered User
 
Join Date: May 2008
Last Activity: 3 October 2013, 1:51 PM EDT
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by rdrtx1 View Post
try fixed:

Code:
awk -F"|" 'NR==FNR {a[$1]=$0; for (i=2; i<=6; i++) b[$1,$i]=b[$1,i]=$i; next}
a[$1] {  
  s=0; 
  for (i=2; i<=6; i++) {if (!b[$1,$i]) s=1};  
  if (s==1) {    
    printf "{ \"";    
    printf substr($8,3) "\", \"";    
    printf b[$1,2]      "\", \"";    
    printf b[$1,3]      "\", \"";    
    printf b[$1,4]      "\", \"";    
    printf b[$1,5]      "\", \"";    
    printf $1           "\", \"";    
    printf substr($7,3) "\", \"";    
    printf $9           "\", \"";    
    printf $15;    
    print  "\" }";  
  }
}' file1 file2 > file3


WORKS PERFECT! I really appreciate your help. You and awk are amazing! I've been scripting with shell for 15+ years but never got to the advanced level of using awk or sed, I really need to invest more time into learning it. Do you do training classes? Thanks again.
Sponsored Links
    #6  
Old 12-11-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 29 August 2014, 3:14 PM EDT
Location: Saskatchewan
Posts: 19,271
Thanks: 774
Thanked 3,236 Times in 3,034 Posts
There's really no substitute for just using it until you're used to it.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
match two key columns in two files and print output (awk) pelhabuan Shell Programming and Scripting 2 09-25-2012 09:35 AM
AWK to match and merge data from 2 files into 1. right_coaster Shell Programming and Scripting 6 06-27-2012 02:38 AM
[Shell/Perl(?)] Prepending timestamps to console output & writing results to a file Vryali Shell Programming and Scripting 5 05-27-2012 06:15 AM
Add fields in different files only if some fields between them match murpholinox Shell Programming and Scripting 10 06-07-2011 12:03 AM
AWK Compare files, different fields, output stacky69 Shell Programming and Scripting 4 11-08-2010 07:54 AM



All times are GMT -4. The time now is 04:08 PM.