Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk - Merge two files based on one key

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk - Merge two files based on one key
# 1  
Old 08-10-2016
awk - Merge two files based on one key
Hi,

I am struggling with the an awk command to merge two files based on a common key.

I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended

File1
Code:
 
COL1|COL2|COL3|COL4|COL5|COL6|COL7
100|"AAA"|"DATE"|"1"|"Y"|"LEVEL"|""
100|"AAA"|"DATE"|"2"|"Y"|"LEVEL"|""
100|"AAA"|"DATE"|"3"|"Y"|"LEVEL"|""
101|"AAA"|"DATE"|"4"|"Y"|"LEVEL"|""
102|"AAA"|"DATE"|"5"|"Y"|"LEVEL"|""
104|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""
105|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""

File2
Code:
 
100|AA
101|BB
102|CC

Desired output
Code:
 
COL1|COL2|COL3|COL4|COL5|COL6|COL7
100|"AAA"|"DATE"|"1"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"2"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"3"|"Y"|"LEVEL"|""|AA
101|"AAA"|"DATE"|"4"|"Y"|"LEVEL"|""|BB
102|"AAA"|"DATE"|"5"|"Y"|"LEVEL"|""|CC
104|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|
105|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|

Any help would be much appreciated,

Thanks
# 2  
Old 08-10-2016
Quote:
Originally Posted by Ads89
Hi,

I am struggling with the an awk command to merge two files based on a common key.

I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended

File1
Code:
 
COL1|COL2|COL3|COL4|COL5|COL6|COL7
100|"AAA"|"DATE"|"1"|"Y"|"LEVEL"|""
100|"AAA"|"DATE"|"2"|"Y"|"LEVEL"|""
100|"AAA"|"DATE"|"3"|"Y"|"LEVEL"|""
101|"AAA"|"DATE"|"4"|"Y"|"LEVEL"|""
102|"AAA"|"DATE"|"5"|"Y"|"LEVEL"|""
104|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""
105|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""

File2
Code:
 
100|AA
101|BB
102|CC

Desired output
Code:
 
COL1|COL2|COL3|COL4|COL5|COL6|COL7
100|"AAA"|"DATE"|"1"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"2"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"3"|"Y"|"LEVEL"|""|AA
101|"AAA"|"DATE"|"4"|"Y"|"LEVEL"|""|BB
102|"AAA"|"DATE"|"5"|"Y"|"LEVEL"|""|CC
104|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|
105|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|

Any help would be much appreciated,

Thanks
Hello Ads89,

Could you please try following.
Code:
awk 'FNR==NR{A[$1]=$NF;next} FNR!=NR && FNR>1{Q=$1;$0=(Q in A)?$0 FS A[$1]:$0 FS;print;next} {print}' FS="|"  Input_file2   Input_file1

Output will be as follows.
Code:
COL1|COL2|COL3|COL4|COL5|COL6|COL7
100|"AAA"|"DATE"|"1"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"2"|"Y"|"LEVEL"|""|AA
100|"AAA"|"DATE"|"3"|"Y"|"LEVEL"|""|AA
101|"AAA"|"DATE"|"4"|"Y"|"LEVEL"|""|BB
102|"AAA"|"DATE"|"5"|"Y"|"LEVEL"|""|CC
104|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|
105|"AAA"|"DATE"|"6"|"Y"|"LEVEL"|""|

Thanks,
R. Singh
These 2 Users Gave Thanks to RavinderSingh13 For This Post:
Ads89 rbatte1
# 3  
Old 08-10-2016
Awesome! Works perfectly - thank you!!
# 4  
Old 08-10-2016
Code:
awk -F"|" 'NR==FNR {a[$1]=$2; next} FNR>1 {$(NF+1)=a[$1]} 1' file2 OFS="|" file1

This User Gave Thanks to rdrtx1 For This Post:
rbatte1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies

2. Shell Programming and Scripting

Files summary using awk based on index key

Hello , I have several files which are looking similar to : file01.txt keyA001 350 X string001 value001 keyA001 450 X string002 value007 keyA001 454 X string002 value004 keyA001 500 X string003 value005 keyA001 255 X string004 value006 keyA001 388 X string005 value008 keyA001 1278 X... (4 Replies)
Discussion started by: alex2005
4 Replies

3. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

4. Shell Programming and Scripting

Merge files based on columns

011111123444 1234 1 20000 011111123444 1235 1 30000 011111123446 1234 3 40000 011111123447 1234 4 50000 011111123448 1234 3 50000 File2: 011111123444,Rsttponrfgtrgtrkrfrgtrgrer 011111123446,Rsttponrfgtrgtr 011111123447,Rsttponrfgtrguii 011111123448,Rsttponrfgtrgtjiiu I have 2 files... (4 Replies)
Discussion started by: vinus
4 Replies

5. Shell Programming and Scripting

Merge multiple lines in same file with common key using awk

I've been a Unix admin for nearly 30 years and never learned AWK. I've seen several similar posts here, but haven't been able to adapt the answers to my situation. AWK is so damn cryptic! ;) I have a single file with ~900 lines (CSV list). Each line starts with an ID, but with different stuff... (6 Replies)
Discussion started by: protosd
6 Replies

6. Shell Programming and Scripting

Merge two files based on a 3rd key file

Hi, I want to merge the two files based on the key file's columns. The key file: DATE~DATE HOUSE~IN_HOUSE CUST~IN_CUST PRODUCT~PRODUCT ADDRESS~CUST_ADDR BASIS_POINTS~BASIS_POINTS ... The other 2 files are From_file & To_file - The From_file: DATE|date/time|29|9 ... (9 Replies)
Discussion started by: dips_ag
9 Replies

7. Shell Programming and Scripting

Gawk / Awk Merge Lines based on Key

Hi Guys, After windows died on my netbook I installed Lubuntu and discovered Gawk about a month ago. After using Excel for 10+ years I'm amazed how quick and easily Gawk can process data but I'm stuck with a little problem merging data from multiple lines. I'm an SEO Consultant and provide... (9 Replies)
Discussion started by: Jamesfirst
9 Replies

8. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

9. Shell Programming and Scripting

merge based on common, awk help

All, $ cat x.txt z 11 az x 12 ax y 13 ay $ cat y.txt ay TT ax NN Output required: y 13 ay TT x 12 ax NN (3 Replies)
Discussion started by: jkl_jkl
3 Replies

10. Shell Programming and Scripting

Merge files based on key

Hi Friends, Can any one help me with merging these file based on two columns : File1: A|123|99|SAMS B|456|95|GEORGE D|789|85|HOVARD File2: S|123|99|NANcY|6357 S|123|99|GREGRO|83748 A|456|95|HARRY|827|somers S|456|95|ANTONY|546841|RUDOLPH|7263 B|456|95|SMITH|827|BOISE STATE|834... (3 Replies)
Discussion started by: sbasetty
3 Replies
Login or Register to Ask a Question