Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Comparision of fields in 2 files.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparision of fields in 2 files.
# 1  
Old 04-09-2012
Comparision of fields in 2 files.
Hi Experts,
I have two huge files in the format as shown below.I need to open a file1 and file 2 , cut first 24 characters of file 1 and search if the key exists
in file 2 first field (delimted by *).

If the value exists , copy the third field from file 2 and replace the 5th field in file 1 .

Any ideas would be helpful!


Format of file 1 :
Code:
20030101302070030608001RNU2003010102121111  -7.0   -2800.00   -1072.91       0.00   -1072.91                2520               PDIEM     60835731   608357            S0024004201127894933185202366                0.0020101111       0.00  GTM     SHSUB       0.00     LATORELLA,JEANETTE M          2 *
20030101302070030608002 PU2003010102121111   7.0    2800.00    1072.91       0.00       0.00                2520               PDIEM     60835731   608357            S0024004201127894933187202366                0.0020101111    1072.91  GTM        SHSUB            0.00     LATORELLA,JEANETTE M          2 *
20030101302262032506001RNU2003011797002     -1.0    -128.00     -83.00       0.00     -83.00                2120               FEESC     68581712   685817            S0041750101229882490710E002230571            0.0020101111       0.00  GTM     BLDAD       0.00     SYBERTS,LORETTA M             21*

Format of File 2 :
Code:
00000204002142043216001R*660440128*0*
00000715007270044409008 *675872820*0*
00001002010262022921001 *685214670*0*
00001002010262022921001R*692908554*0*
00010212104233022619001 *702717449*0*
00010312103260019120001 *699785873*0*
00010412105020016206001 *703965156*0*
00010412105020016206001R*716591137*0*
00010412105020021016001 *703742788*0*
00010412105090037707001 *704417912*0*
00010412105090037707001R*709007350*0*
00010512106040047904001 *706829105*0*
00010702107302005703001 *713546474*0*

# 2  
Old 04-09-2012
Hi, see if this works:
Code:
awk 'NR==FNR{A[$1]=$3;next}{k=substr($1,1,24)} k in A{sub($4 " *" $5, $4 sprintf("%11.2f", A[k]))}1' FS=\* file2 FS=" " file1

# 3  
Old 04-09-2012
nevermind scrutinizer's solutoin is better
# 4  
Old 04-09-2012
Thanks worked! But a condition needs to be added
Wow! it works! the only thing is it got a little complicated for me Smilie
I have to add a condidtion that if the third field in file 2 is 0, replace the 5th field in file1 by spaces.Any easy way of doing it,since I not very good in awk.
# 5  
Old 04-09-2012
Try:
Code:
awk 'NR==FNR{A[$1]=$3;next}{k=substr($1,1,24)} k in A{sub($4 " *" $5, $4 (A[k]?sprintf("%11.2f", A[k]):"           "))}1' FS=\* file2 FS=" " file

Quote:
Originally Posted by nua7
[..]Any easy way of doing it,since I not very good in awk.
What language would you be more comfortable with?

This awk script first puts the relevant fields into an array (the first part where it says NR==FNR. The second part reads the second file and "k in A" determines of the key is present in the array. if so replace field 4 and field 5 by field 4 and a modified field 5 (this is done this way to preserve formatting)..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Is there a UNIX command that can compare fields of files with differing number of fields?

Hi, Below are the sample files. x.txt is from an Excel file that is a list of users from Windows and y.txt is a list of database account. $ head -500 x.txt y.txt ==> x.txt <== TEST01 APP_USER_PROFILE USER03 APP_USER_PROFILE TEST02 APP_USER_EXP_PROFILE TEST04 APP_USER_PROFILE USER01 ... (3 Replies)
Discussion started by: newbie_01
3 Replies

2. Shell Programming and Scripting

Comparision of two data columns in different files

Hi All, I have a requirement to compare data column which is the last field in two different files and trigger and alert if the difference is greater than 1 for each row. File1 Jan Acount1 2014 11223 Feb Account2 2014 2345 Mar Account3 2014 1233 File2 Jan Account1 2014... (1 Reply)
Discussion started by: Naresh Babu
1 Replies

3. Shell Programming and Scripting

Comparision of two text files

Dear all, I am having two files big files i need an output file as first occurance of file1 field in file2 example: file1:raju ranifile2:raju|123 raju|879 rani|623 rani|253result:raju|123 rani|623pls help me in this regard (3 Replies)
Discussion started by: suryanarayana
3 Replies

4. Shell Programming and Scripting

Two files comparision with single field

Hi , Im new to uxin environment and shell scripting.... please help me with the code for the following scenario..... file 1 contains the following fields abc 200 rupee IND cdf 400 dollar USA efg 300 euro GER hij 600 pound ENG file 2 SBI abc 321 dollar CANAD kvr mnd ... (6 Replies)
Discussion started by: shivaji_veer
6 Replies

5. Shell Programming and Scripting

awk comparision between 2 files and substitution in third

Hi All, I have two files in the following format. File 1 : 1044|1|20121031|2910039.4|MR|201210|G1044|E 1082|2|20121031|1664662.84|MR|201210|G1082|E 1696|3|20121031|190801.5|MR|201210|G1696|E 1824|4|20121031|196350|MR|201210|G1824|E 1900|5|20121031|221447.8|MR|201210|G1900|E File 2 :... (7 Replies)
Discussion started by: nua7
7 Replies

6. Shell Programming and Scripting

Add fields in different files only if some fields between them match

Hi everybody (first time posting here) I have a file1 that looks like > 1,101,0.1,0.1 1,26,0.1,0.1 1,3,0.1,0.1 1,97,0.5,0.5 1,98,8.1,0.218919 1,99,6.2,0.248 2,101,0.1,0.1 2,24,3.1,0.147619 2,25,23.5,0.559524 2,26,34,0.723404with 762 lines.. I have another 'similar' file2 > ... (10 Replies)
Discussion started by: murpholinox
10 Replies

7. Shell Programming and Scripting

Comparision of two huge unix files - Reconcilation

Hi, I have two huge file; each one has approximately 150000 lines. I need to compare both of them and store the unmatched lines into a different file. I have searched for everything in google but did not get solution. Files are: File1 NRALBAMINDB20003726 NRALBAMINDB20003727... (16 Replies)
Discussion started by: Suman Singh
16 Replies

8. UNIX for Advanced & Expert Users

Comparision of two files.

File Structure file1.txt.arch 029429288,1,,,02087400376,N,02087400376,N,0,02087400376,N,0,0,8010,08000151736,U,N,,08000151736,U,20100726111237,20100726111237,0,20100726111651,00004140,16,16,10,N;... (1 Reply)
Discussion started by: ravigupta2u
1 Replies

9. Shell Programming and Scripting

Column comparision in two files

Hi, I need to compare a column in two different csv files file1 xyz.com,2/2/12,a,b,c eg.com,2/2/23,a,b,ga file2 1,2,ua,xyz.com 1,2,ua,abc.com 1,2,ua,eg.com 1,2,ua,easg.com 1,2,ua,zth.com Read all entries in file1(which has 1000+) and compare column1 of file1 with the column4... (13 Replies)
Discussion started by: nuthalapati
13 Replies

10. Shell Programming and Scripting

comparision of string in various files

i want to take position 19-24(only first line) from all files and need to compare any duplication is there or not. If duplication, then i have to print the file names. I have written to take the characters from 19-24 from all files. but how to compare ? ... (1 Reply)
Discussion started by: senthil_is
1 Replies
Login or Register to Ask a Question