Join with awk using sprintf


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Join with awk using sprintf
# 1  
Join with awk using sprintf

Hi,

Trying to join 2 files with awk (file1 has variable number of fields; file 2 has constant number of fields)
file1:
Code:
hook1|AA|BB|CC|DD
hook2|EE|FF

file2:
Code:
hook1|11|22
hook2|33|44
hook3|55|66

output:
Code:
hook1|11|22|AA|BB|CC|DD
hook2|33|44|EE|FF
hook3|55|66

What I tried so far:
Code:
gawk -F '|' 'NR==FNR{for(i=2; i<=NF; i++){a[$1]=sprintf("%s|", $i);next}{print $1 FS $2 FS $3 FS a[$1]}' file1 file2

The files join but only with 4 fields (it only takes $2 of file1):
Code:
hook1|11|22|AA
hook2|33|44|EE
hook3|55|66

Same problem with:
Code:
gawk -F '|' 'NR==FNR{for(i=2; i<=NF; i++){a[$1]=$i;next}{printf("%s\n", $1 FS $2 FS $3 FS a[$1]}' file1 file2

Do you have an idea how to join the 2 files without printing $1 of file 1 and with all the leading fields?
# 2  
How about
Code:
awk 'NR==FNR {T[$1]=$0; sub($1"\|", "", T[$1]); next} {print $0, T[$1]} ' FS=\| OFS=\| file1 file2
hook1|11|22|AA|BB|CC|DD
hook2|33|44|EE|FF
hook3|55|66|

This User Gave Thanks to RudiC For This Post:
# 3  
You could also try:
Code:
awk -F'|' 'FNR==NR{s=$1;$1="";d[s]=$0;next}{print $0 d[$1]}' OFS='|' file1 file2

which avoids adding the unwanted trailing | on lines to file2 that don't have an entry in file1 produced by RudiC's suggestion. It produces the output:
Code:
hook1|11|22|AA|BB|CC|DD
hook2|33|44|EE|FF
hook3|55|66

If you want to try either of these awk scripts on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk.
This User Gave Thanks to Don Cragun For This Post:
# 4  
I see the trick. it works great !

Thanks Don Cragun and RudiC !
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #303
Difficulty: Easy
Normally sed is invoked like this: sed SCRIPT INPUTFILE ...
True or False?

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. Shell Programming and Scripting

Using sprintf and system command in awk

Hello Friends, I'm trying something hard (for me) to create a report script,normally the following script works: Echos are just for cosmetic touch, echo -n "\n-----\t----------\t-------------\t\t--------------\t\t--------\n COUNT\tEVENT_TYPE\tRESPONSE_CODE\t\tINTERNAL_ERROR\t\tFLOWNAME... (7 Replies)
Discussion started by: EAGL€
7 Replies

3. UNIX for Dummies Questions & Answers

Join with awk

I have file 1 (f1) 531902021342|610231416030043 531902021421|610231415853764 531902021460|610231406819593 531902021477|610231410151075 531902021481|610230208405255 531902021484|0 531902021557|610231407330625 531902021558|610230208106654 531902021564|610230204101407 file 2(f2)... (7 Replies)
Discussion started by: radius
7 Replies

4. UNIX for Dummies Questions & Answers

Join or Awk

Is is possible to join on unsorted files by a string? Don't want to sort because there is other text that is already in a good format. I want to replace or join RBOSK-374 in file 2 with NB2781 FGH in file 1. Any help would be appreciated. a.log: RBOSK-374 AAA-B04-D16-K01 a.log: 0 XXX 602... (1 Reply)
Discussion started by: jimmyf
1 Replies

5. Shell Programming and Scripting

left join using awk

Hi guys, I need AWK to merge the following 2 files: file1 1 a 1 1 2 b 2 2 3 c 3 3 4 d 4 4 file2 a a/a c/c a/c c/c a/a c/t c c/t c/c a/t g/g c/c c/t desired output: 1 a 1 1 a/a c/c a/c c/c a/a c/t 2 b 2 2 x x x x x x 3 c 3 3 c/t c/c a/t g/g c/c c/t 4 d 4 4 x x x x x x (2 Replies)
Discussion started by: g1org1o
2 Replies

6. Shell Programming and Scripting

Problem with awk awk: program limit exceeded: sprintf buffer size=1020

Hi I have many problems with a script. I have a script that formats a text file but always prints the same error when i try to execute it The code is that: { if (NF==17){ print $0 }else{ fields=NF; all=$0; while... (2 Replies)
Discussion started by: fate
2 Replies

7. Shell Programming and Scripting

Join using awk

Hi - I want to join 2 files on matching keys ( column 1 in both files ) and check for unmatched columns, If for each record print the first unmatched column from both files and then proceed to next record and do the same. File 1: 123|Roy|jj|20/07/3000|25.48 125|Victor|kk|30/07/2009|34.56... (7 Replies)
Discussion started by: nbethy1
7 Replies

8. 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

9. Shell Programming and Scripting

awk for join

Is it possible to do this with awk command?? sort1.txt a 10 b 20 sort2.txt a b c d join command join sort1.txt sort2.txt && join -v1 sort2.txt sort1.txt output a 10 b 20 (1 Reply)
Discussion started by: repinementer
1 Replies

10. Shell Programming and Scripting

awk, join or sed

$ cat file1 a:23:43 A B C a:24:21 a:23:44 S D A F a:24:44 a:23:45 S D E a:24:45 $ cat file2 a:23:53 (1 Reply)
Discussion started by: jkl_jkl
1 Replies

Featured Tech Videos