Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Awk: Replacement using 2 diff files input and comparison

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk: Replacement using 2 diff files input and comparison
# 1  
Old 01-14-2014
Awk: Replacement using 2 diff files input and comparison
Requirement:
If $5(date field) in ipfile is less than $7(date field) in deact file & $1 of ipfile is present in deactfile then
$1 to be replaced by $2,$3,$4,$5,$6 of deact file
else if $5(date field) in ipfile is greater than $7(date field) in actfile & $1 of ipfile is present in actfile then
$1 to be replaced by $2,$3,$4,$5,$6 of actfile.


IPFILE
Code:
8104665534|2|404001742933540|180.215.139.144|20140114100944|20140114101027|43|20140114101045|3756|13054|31|26|59|44700kth|70|1|internet@internet.mtsindia.in|34661
8104665534|2|404001742933541|180.215.139.144|20131114101045|20131115101045|0|20131115101045|0|0|0|0|59|44700kth|68|1|internet@internet.mtsindia.in|34661
8
9136713486|2|405891614985516|180.215.176.110|20140114093453|20140114100922|2069|20140114101001|1658883|196181|114597376|3801088|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9136713486|2|405891614985516|180.215.176.110|20130114093453|20140114094336|523|20140114101001|9270|1799|359414536|2339687199|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9136713486|2|405891614985516|180.215.176.110|20130911093509|20140114093509|0|20140114101001|0|416|1157627956|16384|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9

ACTFILE:
Code:
8104665534|1903726104|1|8182|935674|MB|20140106|2
9136713486|1903743569|1|8182|936308|MB|20140101|6

DEACTFILE:
Code:
8104665534|1903739454|1|8453|185670|MB|20131214|8
9136713486|1903713493|1|8453|151012|MB|20130215|8

Present Code which only does replacement based on $1 checking and not on $5(date field) of ipfile:
Code:
awk -F"|" 'FNR==1 {++counter}
counter==1 {ACTFILE[$1]=$2"|"$3"|"$4"|"$5"|"$6;next}
counter==2 {DEACTFILE[$1]=$2"|"$3"|"$4"|"$5"|"$6;next}
{
if ( $1 in ACTFILE)
print ACTFILE[$1],$8,$9+$10> "ok.txt"
else if ( $1 in DEACTFILE)
print DEACTFILE[$1],$8,$9+$10> "nok.txt"
}
' FS="|" OFS="|" ACTFILE DEACTFILE IPFILE


Please help as i am unable to put the date field checks in the code.
# 2  
Old 01-14-2014
Since you are only comparing dates and one date is in YYmmddHHMMSS format and the other in YYmmdd, you could just slap "000000" at the end of the latter and do a numerical comparison.
This User Gave Thanks to Scrutinizer For This Post:
siramitsharma
# 3  
Old 01-14-2014
Thanks scrutinizer, I will use substr for the date field for the numerical comparison, but getting confused in how to place the required checks in awk(existing code shared), can you suggest the condition for the same in code?
# 4  
Old 01-14-2014
You can try something like this, as Scrutinizer already suggested


Code:
awk    '
  
       FNR==1{
                    ++counter
             }
  counter==1 {
                ACTFILE[$1]=$2 FS $3 FS $4 FS $5 FS $6 FS $7
                next
             }
  counter==2 {
                DEACTFILE[$1]=$2 FS $3 FS $4 FS $5 FS $6 FS $7
                next
             }
function date_format(vin){
                             # For 20140114100944 
                             year = substr(vin,1,4)
                             mon  = substr(vin,5,2)
                             day  = substr(vin,7,2)
                             hr   = substr(vin,9,2)  ? substr(vin,9,2) : 00
                             min  = substr(vin,11,2) ? substr(vin,11,2): 00
                             sec  = substr(vin,13)   ? substr(vin,13)  : 00
                             return year" "mon" "day" "hr" "min" "sec
                         }
              {
                 timeip = $5 ? mktime(date_format($5)) : NULL
                      if( $1 in DEACTFILE){
                                           n =split(DEACTFILE[$1],A)
                                           dfiletime = mktime(date_format(A[n]))
                                          if(timeip < dfiletime)
                                              print A[1],A[2],A[3],A[4],A[5],$8,$9+$10 >"ok_file"
                
                                          } 
                      if( $1 in ACTFILE)  {
                                           n =split(ACTFILE[$1],A)
                                           actfiletime = mktime(date_format(A[n]))
                                           if(timeip > actfiletime)
                                               print A[1],A[2],A[3],A[4],A[5],$8,$9+$10 >"No_ok_file" 
                                          }
              }
           ' FS="|" OFS="|" act deact ip

This User Gave Thanks to Akshay Hegde For This Post:
siramitsharma
# 5  
Old 01-14-2014
Code:
 awk -F"|" 'FNR==1{++cnt}
 cnt==1{ACTFILE[$1]=$2"|"$3"|"$4"|"$5"|"$6;a[$1]=$7"000000";next}
cnt==2{DEACTFILE[$1]=$2"|"$3"|"$4"|"$5"|"$6;d[$1]=$7"000000";next}
{
if (ACTFILE[$1] && $5 gt a[$1] ) {print ACTFILE[$1],$8,$9+$10} else if (DEACTFILE[$1] && $5 lt d[$1] ) {print DEACTFILE[$1],$8,$9+$10}
}' OFS="|" ACTFILE DEACTFILE IPFILE

This User Gave Thanks to pravin27 For This Post:
siramitsharma
# 6  
Old 01-14-2014
Thanks Akshay. The code did worked with minor modifications. Just need another advice in case there are multiple entries in DEACT file then how to handle such scenario based on the different input. Please refer the updated deact file & updated ipfile.

DEACTFILE:
Code:
8104665534|1903739454|1|8453|185670|MB|20131214|8
9136713486|1903713493|1|8453|151012|MB|20130215|8
9136713486|1903713580|1|8453|151012|MB|20130118|8
9136713486|1903713786|1|8453|151012|MB|20130125|8

IPFILE:

Code:
8104665534|2|404001742933540|180.215.139.144|20140114100944|20140114101027|43|20140114101045|3756|13054|31|26|59|44700kth|70|1|internet@internet.mtsindia.in|34661
8104665534|2|404001742933541|180.215.139.144|20131114101045|20131115101045|0|20131115101045|0|0|0|0|59|44700kth|68|1|internet@internet.mtsindia.in|34661
9136713486|2|405891614985516|180.215.176.110|20140114093453|20140114100922|2069|20140114101001|1658883|196181|114597376|3801088|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9136713486|2|405891614985516|180.215.176.110|20130114093453|20140114094336|523|20140114101001|9270|1799|359414536|2339687199|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9136713486|2|405891614985516|180.215.176.110|20130911093509|20140114093509|0|20140114101001|0|416|1157627956|16384|59|46600g0W|70|1|internet@internet.mtsindia.in|32852
9136713486|2|405891614987865|180.215.176.110|20130120093453|20140114094336|523|20140114101001|9270|1799|359414536|2339687199|59|46600g0W|70|1|internet@internet.mtsindia.in|32852

Modified code:

Code:
awk    '

       FNR==1{ ++counter }
  counter==1 { ACTFILE[$1]=$2 FS $3 FS $4 FS $5 FS $6 FS $7;next}
  counter==2 { DEACTFILE[$1]=$2 FS $3 FS $4 FS $5 FS $6 FS $7; next}
function date_format(vin)
{
                             # For 20140114100944 
                             year = substr(vin,1,4)
                             mon  = substr(vin,5,2)
                             day  = substr(vin,7,2)
                             hr   = substr(vin,9,2)  ? substr(vin,9,2) : 00
                             min  = substr(vin,11,2) ? substr(vin,11,2): 00
                             sec  = substr(vin,13)   ? substr(vin,13)  : 00
                             return year" "mon" "day" "hr" "min" "sec
}

{
   timeip = $5 ? mktime(date_format($5)) : NULL
print timeip
   n =split(ACTFILE[$1],A)
   actfiletime = mktime(date_format(A[n])) 
   n =split(DEACTFILE[$1],B)
   dfiletime = mktime(date_format(B[n]))

   if( $1 in ACTFILE && timeip > actfiletime)
                print A[1],A[2],A[3],A[4],A[5],$8,$9+$10 >"AACT_file"
   else if( $1 in DEACTFILE && timeip < dfiletime )  
                print B[1],B[2],B[3],B[4],B[5],$8,$9+$10 >"DACT_file"                                         
  else 
                print $0 > "Reject_file"
}
' FS="|" OFS="|" act deact ip

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk comparison using multiple files

Hi, I have 2 files, I need to use column of file1 and do a comparison on file2 column 1 and print the mismatch is file3 as mentioned below. Kindly consider that file 1 is having uniq key(column) whereas in file2 we have multiple duplicates (like 44). These duplicates should not come in... (2 Replies)
Discussion started by: grv
2 Replies

2. UNIX for Dummies Questions & Answers

diff then awk two files

Hi friends, i am trying to diff two files and the result will be passed to awk and this will get the first 20 characters in every line and put the result in a file.... but i can't generate an output. $ cat file1.txt 1 2 3 4 5 $ cat file2.txt 1 2 3 4 the line of command is: (2 Replies)
Discussion started by: kokoro
2 Replies

3. Shell Programming and Scripting

comparison of 2 files using unix or awk

Hello, I have 2 files and I want them to be compared in a specific fashion file1: A_1200_1250 A_1251_1300 B_1301_1350 B_1351_1400 B_1401_1450 C_1451_1500 and so on... file2: 1210 1305 1260 1295 1400 1500 1450 1495 Now The script should look for "1200" from A_1200_1250 of... (8 Replies)
Discussion started by: Diya123
8 Replies

4. Shell Programming and Scripting

Show the diff in two files using awk

Hi, How can i use AWK or any other commands to find the difference between 2 files. File A aaa bbb ccc 111 222 File B aaa ccc 111 Output bbb 222 (6 Replies)
Discussion started by: gambit97
6 Replies

5. UNIX for Dummies Questions & Answers

df -> output files; comparison using awk or...

:wall: I am trying to do the following using awk (is that the best way?): Read 2 files created from the output of df (say, on different days) and compare the entries using the 1st (FileSys) and 6th (Mount) fields to see if the size has changed. Output (at least), to a new file (some header... (2 Replies)
Discussion started by: renata
2 Replies

6. Shell Programming and Scripting

Comparison and editing of files using awk.(And also a possible bug in awk for loop?)

I have two files which I would like to compare and then manipulate in a way. File1: pictures.txt 1.1 1.3 dance.txt 1.2 1.4 treehouse.txt 1.3 1.5 File2: pictures.txt 1.5 ref2313 1.4 ref2345 1.3 ref5432 1.2 ref4244 dance.txt 1.6 ref2342 1.5 ref2352 1.4 ref0695 1.3 ref5738 1.2... (1 Reply)
Discussion started by: linuxkid
1 Replies

7. Shell Programming and Scripting

Diff between 2 files using awk

Hi Experts, Could you please help me to find the difference between two files. I tried the diff command but did not like the output as it contained < and > signs and the line numbers. Is it possible to do something using awk? I have two files, say File1.txt contains 5 values and File2.txt... (6 Replies)
Discussion started by: forumthreads
6 Replies

8. Shell Programming and Scripting

Awk Comparison of 2 specific files

Hi Everybody, I know the topic sounds familiar but I just couldn't adapt or find the right code that solves my particular issue. I really hope you can help. I would like to compare 2 files in an awk script. Both files have different paths. The awk script call should look like that awk -f... (7 Replies)
Discussion started by: hhoosscchhii
7 Replies

9. Shell Programming and Scripting

Comparison of two files in awk

Hi, I have two files file1 and file2 delimited by semicolon, And I want to compare column 2 and column3 of file1 to column3 and column 4 in file2. file1 -------- abc;cef;155.67;143_34; def;fgh;146.55;123.3; frg;hff;134.67;; yyy;fgh;134.78;35_45; file 2 --------- abc;cef;155.09;;... (12 Replies)
Discussion started by: jerome Sukumar
12 Replies

10. Shell Programming and Scripting

String Comparison between two files using awk

I have two files with field seperator as "~". File A: 12~13~14~15 File B: 22~22~32~11 i want to calculate the difference between two files and than calculate the percentage difference and output it to a new file. How do i do this using awk. Also please suggest GOOD awk tutorials. Thank... (7 Replies)
Discussion started by: rudoraj
7 Replies
Login or Register to Ask a Question