Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Join with awk using sprintf

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Join with awk using sprintf
# 1  
Old 06-30-2015
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  
Old 06-30-2015
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:
beca123456
# 3  
Old 06-30-2015
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:
beca123456
# 4  
Old 06-30-2015
I see the trick. it works great !

Thanks Don Cragun and RudiC !
 
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. 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. Shell Programming and Scripting

Join two files using awk

Hello All; I have two files: File1: abc def pqr File2: abc,123 mno,456 def,989 pqr,787 ghj,678 (6 Replies)
Discussion started by: mystition
6 Replies

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

5. Shell Programming and Scripting

awk join 2 files

Hello All, file1 A1;B1;C1;D1;E1;F1;G1;H1;III1;J1 A2;B2;C2;D2;E2;F2;G2;H2;III2;J2 A3;B3;C3;D3;E3;F3;G3;H3;III3;J3 A4;B4;C4;D4;E4;F4;G4;H4;III4;J4file2 III1 ZZ1 S1 Y 1 P1 None NA III2 ZZ2 S2 Y 3 P2 None NA III3 ZZ3 S2 Y 5 ... (2 Replies)
Discussion started by: vikus
2 Replies

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

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

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

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

10. 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
Login or Register to Ask a Question