Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace data of one column with data on other file corresponding to transaction ID matched
# 1  
Old 12-22-2010
Question Replace data of one column with data on other file corresponding to transaction ID matched
Hi All,

I have two files one of which having some mobile numbers and corresponding value whose sample content as follows:
Code:
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(,):
Code:
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:
Code:
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  
Old 12-22-2010
Code:
 
awk -F, 'NR==FNR{a[$1]=$2;next;}{if(a[$8]) $18=a[$8];print;}' OFS=, firstFile secondFile

Last edited by anurag.singh; 12-22-2010 at 03:31 PM..
# 3  
Old 12-22-2010
Question Thanks
Quote:
Originally Posted by anurag.singh
Code:
 
awk -F, 'NR==FNR{a[$1]=$2;next;}{if([a[$8]) $18=a[$8];print;}' firstFile secondFile

Thanks Anurag for your help.
It will be great if you can help me understanding the code.
# 4  
Old 12-22-2010
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  
Old 12-22-2010
Bug Thanks
Quote:
Originally Posted by anurag.singh
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)

Thanks for making me the understand of code.
Please guide me where i have to set OFS to comma.
Thanks in advance.
# 6  
Old 12-22-2010
Pls look at post #2 (My 1st response in this thread).
# 7  
Old 12-22-2010
Question Thanks
Quote:
Originally Posted by anurag.singh
Pls look at post #2 (My 1st response in this thread).
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 ----------

Quote:
Originally Posted by anurag.singh
Pls look at post #2 (My 1st response in this thread).
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:
Code:
-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$
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Change data in one column with data from another file's column

Hello, I have this file outputData: # cat /tmp/outputData __Capacity^6^NBSC01_Licences^L3_functionality_for_ESB_switch __Capacity^2100^NBSC01_Licences^Gb_over_IP __Capacity^1837^NBSC01_Licences^EDGE_BSS_Fnc __Capacity^1816^NBSC01_Licences^GPRS_CS3_and_CS4... (1 Reply)
Discussion started by: nypreH
1 Replies

2. Shell Programming and Scripting

Replace First Column and First Row Data

HI Guys, I just want to replace data for First Column and Row Cell(1,1) Input :- Hello A B C X 1 2 3 Y 4 5 6 Z 7 8 9 Output:- Byee A B C X 1 2 3 Y 4 5 6 Z 7 8 9 From Hello to Byee .....And The Each file have Different String. (3 Replies)
Discussion started by: pareshkp
3 Replies

3. Shell Programming and Scripting

Replace data of a file with data from another file using shell scripting.

Dears, I'm new to shell scripting and i was wondering if you can help me with following matter. I have a file containing 400,000 records. The file contains two columns like: 00611291,0270404000005453 25262597,1580401000016155 25779812,1700403000001786 00388934,1200408000000880... (1 Reply)
Discussion started by: paniklas
1 Replies

4. Shell Programming and Scripting

Generate tabular data based on a column value from an existing data file

Hi, I have a data file with : 01/28/2012,1,1,98995 01/28/2012,1,2,7195 01/29/2012,1,1,98995 01/29/2012,1,2,7195 01/30/2012,1,1,98896 01/30/2012,1,2,7083 01/31/2012,1,1,98896 01/31/2012,1,2,7083 02/01/2012,1,1,98896 02/01/2012,1,2,7083 02/02/2012,1,1,98899 02/02/2012,1,2,7083 I... (1 Reply)
Discussion started by: himanish
1 Replies

5. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

6. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

7. Shell Programming and Scripting

Help with replace column data with specific word

Input file: Populus_trichocarpa 30 0 50 0 0 US Vitis_vinifera 1 18 2 8 6 US Populus_trichocarpa 1 5 100 0 0 US Arabidopsis_lyrata_subsp._lyrata 0 90 0 0 0 US Glycine_max 0 2 3 0 70 UK Desired output file: Populus_trichocarpa YES NO YES NO NO US Vitis_vinifera YES YES YES YES YES US... (4 Replies)
Discussion started by: perl_beginner
4 Replies

8. Shell Programming and Scripting

awk/sed to search & replace data in first column

Hi All, I need help in manipulating the data in first column in a file. The sample data looks like below, Mon Jul 18 00:32:52 EDT 2011,NULL,UAT Jul 19 2011,NULL,UAT 1] All field in the file are separated by "," 2] File is having weekly data extracted from database 3] For eg.... (8 Replies)
Discussion started by: gr8_usk
8 Replies

9. Shell Programming and Scripting

Find and replace data in text file with data in same file

OK I will do my best to explain what I need help with. I am trying to format an ldif file so I can import it into Oracle oid. I need the file to look like this example. Keep in mind there are 3000 of these in the file. changetype: modify replace: userpassword dn:... (0 Replies)
Discussion started by: timothyha22
0 Replies

10. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies
Login or Register to Ask a Question