Join with awk different column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Join with awk different column
# 1  
Old 04-14-2015
Join with awk different column

hi guys,

i need help

I need to join file2 to file1 when column 3 in my file1 and column 1 in my file2 in the same string

file1
Code:
AA|RR|ESKIM
RE|DD|RED
WE|WW|SUPSS

file2
Code:
ESKIM|ES
SUPSS|SS

Output
Code:
AA|RR|ESKIM|ES
RE|DD|RED|
WE|WW|SUPSS|SS

I did this
Code:
awk -F'|' 'NR==FNR {h[$3] = $1; next} {FS=OFS="|";print $0,h[$3]}' file2 file1


Last edited by radius; 04-14-2015 at 09:10 AM..
# 2  
Old 04-14-2015
Try:
Code:
awk -F'|' 'NR==FNR {h[$1] = $2; next} {FS=OFS="|";print $0,h[$3]}' file2 file1

or (no need to set FS and OFS on every line):
Code:
awk -F'|' -v OFS='|' 'NR==FNR {h[$1] = $2; next} {print $0,h[$3]}' file2 file1

This User Gave Thanks to Scrutinizer For This Post:
# 3  
Old 04-14-2015
may you explain the code mr Scrutinizer?
# 4  
Old 04-15-2015
Does this help?
Code:
awk -F'|' -v OFS='|' '	# Set input and output field separators to "|" and start the script text.
NR==FNR {		# For lines read from the 1st input file...
	h[$1] = $2	# Set the translation table for the key found in field 1 to the data found in field 2.
	next		# Skip to next input line without processing remaining steps in this awk script.
}
{	print $0,h[$3]	# For lines from the 2nd input file, print the input line data adding a field
			# containing the data found in the first file using field 3 as the key.
}' file2 file1		# End the awk script and specify the files to be processed.

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Programming

Update a column from a Join

Here is my select that I have to identify the child records that are Open (e.c7 < 6000) when the parent (t2068) c.c7 > 3 SELECT c.c1000000161, c.c7, c.c1000000019, e.c1000000829 FROM t2068 c INNER JOIN t1533 e ON e.c1000000829 = c.c301572100 where c.c7 > 3... (2 Replies)
Discussion started by: newborndba
2 Replies

3. Shell Programming and Scripting

Multi column join

Hello folks, having a new problem with an old solution I previously had working, but think i'm missing something here. File1: 900001093|HAMU1|SUDO_ALIAS 100100361|IAM_IDS|SUDO_ALIAS 100100361|AMPF|SUDO_ALIAS File2: ABC123456|Cust1|900001093|myemail@here.com|Jane Smith|Win1|hamu1... (2 Replies)
Discussion started by: dagamier
2 Replies

4. UNIX for Dummies Questions & Answers

Join files by second column

I have file input file1 1/1/2013 A 553.0763397 96 16582 1/1/2013 B 459.8333588 195 11992 1/2/2013 A 844.2973022 306 19555 1/2/2013 B 833.9300537 457 20165 1/3/2013 A 563.6917419 396 13879 1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 Replies

5. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

6. Shell Programming and Scripting

join two column

Hi I want to join last two column: File A U3268 2689 61 12 10 U3268 2684 71 13 0 U3268 2685 81 13 1 Output: U3268 2689 61 12/10 U3268 2684 71 13/0 U3268 2685 81 13/1 Thanks (6 Replies)
Discussion started by: pareshkp
6 Replies

7. Shell Programming and Scripting

Join and awk max column

Hi Friends, I have a file1 with 3400 records that are tab separated and I have a file2 with 6220 records. I want to merge both these files. I tried using join file1 and file2 after sorting. But, the records should be (3400*6220 = 21148000). Instead, I get only around 11133567. Is there anything... (13 Replies)
Discussion started by: jacobs.smith
13 Replies

8. Shell Programming and Scripting

Join multiple files by column with awk

Hi all, I searched through the forum but i can't manage to find a solution. I need to join a set of files placed in a directory (~1600) by column, and obtain an output with first and second column common to each file, but following columns are taken from the file in the list (precisely the fourth... (10 Replies)
Discussion started by: macsx82
10 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files using first column

Hi, I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this: File1 AAA 26 49 7 27 36 33 46 75 73 69 AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies

10. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies
Login or Register to Ask a Question