2

I have 2 files with * delimiter, each file with 3k records.

There are common fields in different positions. In file1(count=1590) the position is 1 and in file2(2707) the position is 2. file2 count and output count should be same. Note: in file2 2nd position numbers will be present in file1 we need to take corresponding $3 value which is 1 or 0

In both files total count was 3k, both files were * delimter, in that file1 $1 and file2 $2 was common field for both files, we need check whether common field has 0 or 1 which present in file1 $3. we need to write the file like 1==>000000001D0560020011 2==>000000003D0792917850, $1=seqno,$2=matched9digit value follwed byD and $3 whether is 0 or 1

All $2 values from file2 will be present as $1 values in file1.

file1:

D056002001**1
D005356216**1
D079291785**0
D610350290**1

file2:

000000001*D056002001
000000002*D610350290
000000003*D079291785

output:

000000001*D056002001*1
000000002*D610350290*1
000000003*D079291785*0

I tried using the following awk commands:

awk -F'*' 'NR==FNR{c[$1]++;next};c[$2]' file1 file2 > output
awk -F"*" '{ OFS="*"; if (NR==FNR) { a[$1+0]=$0;} else { if (a[$1+0]) { print $1, a[$2+0]}}}' file1 file2 > output
awk -F"*" '{ OFS="*"; if (NR==FNR) { a[$1+0]=$0;NEXT; } else { if (a[$2+0]) { print $0,a[$2+0]; } else { print $0,"***"; }}}' file1 file2 > output
awk -F"*" '{ OFS="*"; if (NR==FNR) {a[$1]=1; b[$1]=$2;next;} else { if ( a[$1]==1) { print $0,b[$1]} else { print $0,"0";}}}' file1 file2 > output

Please help on that?

1
  • Is the order of the columns in the output important?
    – pLumo
    Apr 16, 2021 at 14:04

3 Answers 3

7
awk 'BEGIN{FS=OFS="*"} NR==FNR{map[$1]=$3; next} {print $0, map[$2]}' file1 file2
000000001*D056002001*1
000000002*D610350290*1
000000003*D079291785*0
0
2

Use join:

join -t '*' -1 2 -2 1  <(sort -t'*' -k2 file2) <(sort file1) 

Output is a bit different, but from here it should be easy to go on:

D056002001*000000001**1
D079291785*000000003**0

Output all lines from file1:

join -a 1 -t '*' -1 2 -2 1  <(sort -t'*' -k2 file2) <(sort file1)
D056002001*000000001**1
D079291785*000000003**0
D610350290*000000002
1
  • Not sure what you mean, it is the same as your output, just in different order of fields. Please review your question.
    – pLumo
    Apr 16, 2021 at 14:24
0
awk -F "*" 'NR==FNR{b[FNR]=$3;a[$1];next}($2 in a){print $0"*"b[FNR]}' file1 file2

output

000000001*D056002001*1
000000002*D610350290*1
000000003*D079291785*0

You must log in to answer this question.