Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk join 2 files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk join 2 files
# 1  
Old 06-09-2015
awk join 2 files
Hello All,

file1
Code:
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;J4

file2
Code:
III1 ZZ1     S1        Y    1     P1      None    NA
III2 ZZ2     S2        Y    3     P2      None    NA
III3 ZZ3     S2        Y    5     P1      PP    QQQQ1
III5 ZZ4     S2        Y    6     P1      PP    QQQQ2

output
Code:
A1;B1;C1;D1;E1;F1;G1;H1;III1;J1;P1;None;NA
A2;B2;C2;D2;E2;F2;G2;H2;III2;J2;P2;None;NA
A3;B3;C3;D3;E3;F3;G3;H3;III3;J3;P1;PP;QQQQ1
A4;B4;C4;D4;E4;F4;G4;H4;III4;J4;NA;NA;NA

for now I have something like this, but is not working
Code:
awk 'FNR==NR{f2[$1]=$1;next}{for(i=1;i<=NF;i++) if($i in f2) $i=$i FS f2[$i]}1' FS=';' OFS=';' file2 file1

Can someone help?
# 2  
Old 06-09-2015
Code:
$ awk 'FNR==NR { f2[$1]=$6 OFS $7 OFS $8; next} { if ($9 in f2) { print $0, f2[$9] } else { print $0,"NA","NA","NA" } }' OFS=';' file2 FS=';' file1
A1;B1;C1;D1;E1;F1;G1;H1;III1;J1;P1;None;NA
A2;B2;C2;D2;E2;F2;G2;H2;III2;J2;P2;None;NA
A3;B3;C3;D3;E3;F3;G3;H3;III3;J3;P1;PP;QQQQ1
A4;B4;C4;D4;E4;F4;G4;H4;III4;J4;NA;NA;NA

These 2 Users Gave Thanks to neutronscott For This Post:
MadeInGermany vikus
# 3  
Old 06-09-2015
Good solution.
Note the late position of FS=';': process file2 with the default=whitespace FS then change FS then process file1.
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

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

3. Shell Programming and Scripting

awk program to join 2 fields of different files

Hello Friends, I just need a small help, I need an awk program which can join 2 fields of different files which are having one common field into one file. File - 1 FileName~Size File- 2 FileName~Date I need the output file in the following way O/P- File FileName~Date~Size For... (4 Replies)
Discussion started by: abhisheksunkari
4 Replies

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

5. Shell Programming and Scripting

Awk - join multiple files

Is it possible to join all the files with input1 based on 1st column? input1 a b c d e f input2 a b input3 a e input4 c (2 Replies)
Discussion started by: quincyjones
2 Replies

6. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2... (2 Replies)
Discussion started by: mindfreak
2 Replies

7. Shell Programming and Scripting

how to join two files with awk.

Hi, Unix Gurus, I need to compare two file based on key value and load result to different files. requirement as following: file1 1, abc 2, bcd 4, cdefile2 1, aaaaa 2, bbbbb 5, ccccckey value is first column for both file. I need generate following files; records_in_1_not_2.txt 4,... (6 Replies)
Discussion started by: ken002
6 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 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

Left join on files using awk

nawk 'NR==FNR{a;next} {if($1 in a) print $1,"Found" else print}' OFS="," File_B File_A The above code is not working help is appreciated (6 Replies)
Discussion started by: pinnacle
6 Replies
Login or Register to Ask a Question