Hi,

I have two files with the format shown below. I need to read first field(value before comma) from file 1 and search for a record in file 2 that has the same value in the field "KEY=" and write the complete record of file 2 with corresponding field 2 of the first file in to result file.

File 1:

000000000160191837,00140000637006925269
000000000160191837,00140000637006925270
000000000160191838,00140000637006925271
000000000160191840,00140000637006925272

File 2:

<DATA1><#KEY=000000000160191837><DATA2>
<DATA3><#KEY=000000000160191837><DATA4>
<DATA5><#KEY=000000000160191838><DATA6>
<DATA6><#KEY=000000000160191840><DATA8>

Result File:

<DATA1><#KEY=000000000160191837><DATA2><RESULT>00140000637006925269
<DATA3><#KEY=000000000160191837><DATA4><RESULT>00140000637006925270
<DATA5><#KEY=000000000160191838><DATA6><RESULT>00140000637006925271
<DATA6><#KEY=000000000160191840><DATA8><RESULT>00140000637006925272

I wrote awk command for it but my code doesn't take care of duplicate records. please look at first two records in File 1 in the above example, field 1 is same but field 2 is different. In the same way I will have two exact same entries (same KEY value) in File 2 and I need to assign different values.

My code:

Thanks in advance!

nawk -f king.awk FS=, file1 FS='(#KEY=|>)' file2

king.awk:

Thanks for quick reponse!

The code is kind of suppressing duplicates and it's not giving corresponding field 2 of file 1 in the output. I need all records in the output with different field 2 values for duplicates as I shown in the example.

I'm just asking, does it require muti-dimensional array to store different values for duplicates. Not sure as I'm not good at using multi-dimensional arrays.

sorry - misread the data samples.

Assuming there're equal number of same 'keys' in file1 and file2.

king.awk:

Perfect!! Thanks a lot!!!
It works great!! I never thought it in that angle.

I added one more part, please check and let me know if I did it right.
If there is no match for a value in file 2 then I need to take first 11 digits from any value and append zeros to it and output the record.

It was working fine before but now it's not working not sure where I went wrong.

Addition:

FNR==NR{f1[$1]=($1 in f1)? f1[$1] SUBSEP $2 : $2;next}
$3 in f1 {
n=split(f1[$3], a, SUBSEP)
delete f1[$3]
printf("%s<RESULT>%s\n", $0, a[1])
for(i=2;i<=n;i++)
f1[$3]=(i==2)?a[i]:f1[$3] SUBSEP a[i] ; next}
for ( temp in f1) {
tmp_value=substr(f1[temp],1,11)
print $0 "<RESULT>" tmp_value "000000000"
}

Thanks!! You are the best!!
BTW Thanks for calling the awk code as king.awk

This is not giving the desired results if the missing record is last one in the file 2. I figured it out, as we are deleting the array element everytime and when we reach last record we would have deleted all array elements and so it's not printing the last record.

I changed the code a liitle bit and it's working fine now.

FNR==NR{f1[$1]=($1 in f1)? f1[$1] SUBSEP $2 : $2; default_num=$2;next}
$3 in f1 {
n=split(f1[$3], a, SUBSEP)
delete f1[$3]
printf("%s<RESULT>%s\n", $0, a[1])
for(i=2;i<=n;i++)
f1[$3]=(i==2)?a[i]:f1[$3] SUBSEP a[i]
next
}
{
print $0 "<RESULT>" substr(default_num, 1, 11) "000000000"
}

This is my first post to this forum and I'm really astonished with the quality/quick response.