compare date and time inside data of two files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting compare date and time inside data of two files
# 1  
Old 06-01-2011
compare date and time inside data of two files

i have two files with identical no of columns. 6th columns is date (MM/DD/YY format) and 7th columns is time (HH:MM:SS) format. I need to compare these two vaules and if the date & time is higher than fileA, save it on fileC; if the value is lower, then save it on fileD

CONDITIONS
---------------
values from columns 2 to 5 of a raw of fileA is exactly the same as fileB
if the above condition met, then
6th & 7th columns (date & time) of each raw of fileA is higher than fileB
if both the above conditions met, put the entries of fileB which matches, on a new file fileC
if any of the above conditions are not meeting, then put those unmatching entries of fileA on new file fileD

Note :- each raw of fileA need to be compared with all the raws in fileB
fileA
-----
150 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:24:41
129 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:58:22
6 pmemcfdb001-b ERTL Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:03:18

fileB
-----
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/22/11 11:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 09:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 12:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:29:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:24:55
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/24/11 11:24:41
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/23/11 19:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 09:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 21:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:58:32
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/25/11 19:58:22

---------- Post updated at 05:36 AM ---------- Previous update was at 05:35 AM ----------

DESIRED OUTPUT

fileC
-----
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 12:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:29:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:24:55
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/24/11 11:24:41
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 21:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:58:32
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/25/11 19:58:22

fileD
----
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/22/11 11:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 09:24:41
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/23/11 19:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 09:58:22

Last edited by ajiwww; 06-01-2011 at 07:57 AM..
# 2  
Old 06-01-2011
Code:
I need to compare these two vaules and if the date & time is higher than fileA

Compare with what, you have multiple rows in fileA and fileB , how these rows have to be compared.

Is it max date time in file A or what ?
# 3  
Old 06-01-2011
i need to compare the 6th & 7th (date & time) column of each raw of fileA with fileB. If the values are higher, then paste the output on new file fileC and of not, paste it on fileD
# 4  
Old 06-01-2011
You aren't helping us to understand, you 3 rows in file A and 10 rows in file b. Is it one to one comparison or you have some other criteria. Should I take the first row of A and first row of B and compare their date time and so on ?

Think from our perspective and provide the compare condition.
# 5  
Old 06-01-2011
CONDITIONS
---------------
values from columns 2 to 5 of a raw of fileA is exactly the same as fileB
if the above condition met, then
6th & 7th columns (date & time) of each raw of fileA is higher than fileB
if both the above conditions met, put the entries of fileB which matches, on a new file fileC
if any of the above conditions are not meeting, then put those unmatching entries of fileA on new file fileD

Note :- each raw of fileA need to be compared with all the raws in fileB


Example :-
------------
take first line of fileA and check for any entries in fileB which matches from column 2 to 5. If matches, then go to next condition
from the matching entries, check that 6th & 7th column value of fileB is higher than fileA
if both above conditions met, save those matching entries on a new file fileD
if any of the above conditions not met, save those unmatching entries of fileA on fileD

hope this helps..
# 6  
Old 06-01-2011
Code:
awk -F"[ /:]" '{
key=$2$3$4$5;
b_val="20"$8$7$6$9$10$11;

if(NR==FNR)
{
    A[key]="20"$8$7$6$9$10$11
}
else if(A[key] > b_val)
{ 

    system("echo "$0" >>d.txt")
}
else
{
    system("echo "$0" >>c.txt")
}

}' filea.txt fileb.txt

You will have outputs in c.txt and d.txt
# 7  
Old 06-01-2011
thanks for the script, its partially successful Smilie

its not checking columns 2 - 5 of fileA is exactly same as in fileB Smilie
date and time wise checking is perfect

---------- Post updated at 10:34 AM ---------- Previous update was at 10:13 AM ----------

this is the output. text in bold on should not come on fileC. as per first condition column 2 -5 of fileA should be same as fileB.

bash-3.00$ more c.txt
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 12:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:29:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:24:55
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/24/11 11:24:41
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 21:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:58:32
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/25/11 19:58:22
0 pmemcfdb001-b ERTL Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 11:03:18
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:32
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:21
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 19:59:66
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/25/11 19:59:66
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 05/24/11 19:59:66
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 05/24/12 19:59:66
0 xxxx IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/24/11 11:24:41
0 ebs-sql1-b xxxxx Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/23/11 19:58:22
0 pmemcfdb001-b ERTL xxxx Memphis-Prod-SQL-Full-Application-Backup 04/23/11 01:03:18



bash-3.00$ more d.txt
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/22/11 11:24:41
0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 09:24:41
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/23/11 19:58:22
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 04/24/11 09:58:22
0 pmemcfdb001-b ERTL Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/22/11 11:03:18
0 pmemcfdb001-b ERTL Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup 04/23/11 01:03:18
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 05/24/10 19:59:66
0 ebs-sql1-b EBSCClaimStore Memphis_Prod_SQL_Diff Memphis-Prod-SQL-Inc-Application-Backup 03/24/11 19:59:66
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to replace a parameter(variable) date value inside a text files daily with current date?

Hello All, we what we call a parameter file (.txt) where my application read dynamic values when the job is triggered, one of such values are below: abc.txt ------------------ line1 line2 line3 $$EDWS_DATE_INSERT=08-27-2019 line4 $$EDWS_PREV_DATE_INSERT=08-26-2019 I am trying to... (1 Reply)
Discussion started by: pradeepp
1 Replies

2. Shell Programming and Scripting

Shell script to compare two files of todays date and yesterday's date

hi all, How to compare two files whether they are same are not...? like i had my input files as 20141201_file.txt and 20141130_file2.txt how to compare the above files based on date .. like todays file and yesterdays file...? (4 Replies)
Discussion started by: hemanthsaikumar
4 Replies

3. Shell Programming and Scripting

Need to replace the date inside a node of several rdf files

Hi, I have a rdf zip file. This zip file consists of several *.rdf files. I need to replace the date (this is different for each rdf) inside the node "Date_de_Publication_Periodique" of these rdf files. e.g., awk '/Date_de_Publication_Periodique/ && /XMLSchema#date/' MM_NN-A1B1C1_ABC.rdf ... (11 Replies)
Discussion started by: Ribosome
11 Replies

4. Shell Programming and Scripting

Sorting data file by date and time

Hello. Sorting data file by date and time with the following issues: Date is in the following format m/d/yyyy, no leading zeros Time is in the following format h:m:s AM/PM, no leading zeros Any ideas on how to sort data when the above issues? Could the date/time be converted inline to... (5 Replies)
Discussion started by: JimBurns
5 Replies

5. Shell Programming and Scripting

need help to compare 2 files inside a loop

Basically, I have a user input a colour into a variable, and then i echo that variable into a text file. Then I need to compare those 2 files which is easy using the diff command. The thing I can not get is how to do this inside of a loop until the variable matches the file. Also if the user enters... (6 Replies)
Discussion started by: cstadnyk1
6 Replies

6. Shell Programming and Scripting

ksh compare dates INSIDE a file (ie date A is > date B)

In KSH, I am pasting 2 almost identical files together and each one has a date and time on each line. I need to determine if the first instance of the date/time is greater than the 2nd instance of the date/time. If the first instance is greater, I just need to echo that line. I thought I would... (4 Replies)
Discussion started by: right_coaster
4 Replies

7. Shell Programming and Scripting

Split Date/time data to two

Hi all, I have logs(in a log file) with the following structure 20100916011501559;0.812;null;TRUE;;FALSE;0.812;0;0;;19 20100916011504762;0.015;null;TRUE;;FALSE;0;4|4;0.015;;4 20100916011504762;0;null;TRUE;;FALSE;0;0;0;;4 20100916011501731;3.343;null;TRUE;;FALSE;3.156;131|65;0.172;;11... (14 Replies)
Discussion started by: babai
14 Replies

8. Shell Programming and Scripting

time from 2 files to compare

In first file say first.txt, i have a content say 14:56. In second file, say second.txt i have content say 16:01.... I want to compare if these two times in these 2 files are having a difference of 15 minutes...Can any one please help? (2 Replies)
Discussion started by: manoj.b
2 Replies

9. Shell Programming and Scripting

List Files + data inside files

Hey everyone, I'm trying get a list of files using but also append some data located inside the file. The log files contain two strings that I am trying to extract. These strings are surrounded by tags. Here is a sample log file: ... (2 Replies)
Discussion started by: ToeLint
2 Replies

10. UNIX for Dummies Questions & Answers

Compare Date And Time

Hi can somebody send me the code for compare two date and run a shell script. following is the code which i actully want but I am getting errors. regard Jamil . /opt/home/rep/.profile #!/bin/sh VALUE=`sqlplus -silent rep/Ndk38f7@dw <<END set pagesize 0 feedback off verify off... (5 Replies)
Discussion started by: Jamil Qadir
5 Replies
Login or Register to Ask a Question