Replace data of one column with data on other file corresponding to transaction ID matched

Shell Programming and Scripting
1

Hi All,

I have two files one of which having some mobile numbers and corresponding value whose sample content as follows:

9058629605,8.0
9122828964,30.0

And in second file complete details of all mobile numbers and sample content as follows and delimeter used is comma(,):

SDP-DM-100689014,2010-12-21 19:24:29 GMT+05:30,CRBT,18,UK,Default,B_11170244,9058629605,405818120653083,,Pre-Paid,Comviva,default_provider,009114500001366,,0.0,code0,0,,Caller Tune,,UK#11561723,,1292939663242,VAS0003ALL,52211,,,UK,,0.0,Grace,
SDP-DM-100689202,2010-12-21 19:24:34 GMT+05:30,Subscription,4,BJ,,B_12326406,9122828964,405876120814560,,Pre-Paid,,default_provider,4945,,40.0,,0,,,,BJ#12106840,,-2206657c%3A12d08f49330%3A4812,VAS0003ALL,,OBD,RECURRING,BJ,,,,

And i want is if column number 1 of first file which is mobile number matches with column number 8 of second file which is mobile number than 18th column of second file is replaced with data provided on first file with the respective mobile number.

Example of output as follows:

SDP-DM-100689014,2010-12-21 19:24:29 GMT+05:30,CRBT,18,UK,Default,B_11170244,9058629605,405818120653083,,Pre-Paid,Comviva,default_provider,009114500001366,,0.0,code0,8.0,,Caller Tune,,UK#11561723,,1292939663242,VAS0003ALL,52211,,,UK,,0.0,Grace,
SDP-DM-100689202,2010-12-21 19:24:34 GMT+05:30,Subscription,4,BJ,,B_12326406,9122828964,405876120814560,,Pre-Paid,,default_provider,4945,,40.0,,30.0,,,,BJ#12106840,,-2206657c%3A12d08f49330%3A4812,VAS0003ALL,,OBD,RECURRING,BJ,,,,

Please guide me for the above.
Thanks in advance

2 
 
awk -F, 'NR==FNR{a[$1]=$2;next;}{if(a[$8]) $18=a[$8];print;}' OFS=, firstFile secondFile
3

Thanks Anurag for your help.
It will be great if you can help me understanding the code.

4

Did a correction in my earlier post, set OFS to comma.
Here NR==FNR is true for 1st file only. So for data in 1st file, array is created and value is loaded in that. Later while 2nd file processing, 18th field is replaced by value from array (value from 1st file)

5

Thanks for making me the understand of code.
Please guide me where i have to set OFS to comma.
Thanks in advance.

6

Pls look at post #2 (My 1st response in this thread).

7

I got it
Thank you so much for your help.

---------- Post updated 12-23-10 at 12:55 AM ---------- Previous update was 12-22-10 at 11:55 PM ----------

Hi Anurag,

Apologies for dusturbing you agian.When i try to execute the query mention by you in post #2, I am getting the following error:

-bash-3.2$ awk -F, 'NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}' OFS=, firstFile secondFile
awk: NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}
awk:                            ^ syntax error
awk: NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}
awk:                                  ^ syntax error
-bash-3.2$ awk -F, 'NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}' OFS=, firstFile secondFile
awk: NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}
awk:                            ^ syntax error
awk: NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}
awk:                                  ^ syntax error
-bash-3.2$ ll
total 8
-rw-r--r-- 1 ocsg ocsg  31 Dec 23 00:48 firstFile
-rw-r--r-- 1 ocsg ocsg 469 Dec 23 00:48 secondFile
-bash-3.2$
8

My mistake. if([a[$8]) should be if(a[$8]) i.e. remove [ before a
Corrected the command in post #2.

9

Thanks again.
Its working now.