Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to filter data based on sysdate

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to filter data based on sysdate
# 1  
Old 11-25-2014
Need to filter data based on sysdate
I have a file from which I need to filter out certain lines when field 17 is less than sysdate. The file has date in YYYYMMDD HH:MI:SS format.
Sample file is as below:

Code:
PRUM,67016800          ,CC ,C1,67016800          ,00,Y,Y,2 ,US,BX,BOX                                     ,00000001,EA,00000001,20141120 00:00:00,                 ,N,Y,Y,US CUSTOM ,IN,000000047.0000,000000000.7500,000000005.7500,CI,000000202.6875,LB,000000000.6000,000000000.6000,20705032020722,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975284

# 2  
Old 11-25-2014
With what we showed you in your last thread; what have to done to try to solve this problem yourself?
# 3  
Old 11-25-2014
I am trying to do something like this:
Code:
TODAYS_DATE=`date "+%Y%m%d %H:%M:%S"`
echo $TODAYS_DATE
awk 'BEGIN{FS=",";OFS=","} ($17 >  $TODAYS_DATE) {print $0}' file.txt

# 4  
Old 11-25-2014
Hello mady135,

Could you please try following and let us know if this helps. Let's say folowing is the input file.

Input file:
Code:
cat file347
PRUM,67016800          ,CC ,C1,67016800          ,  ,Y,Y,2 ,CK,BX,FOX                                     ,00000001,EA,00000001,20141120 00:00:00,               ,N,Y,Y,CK ABCDEF ,IN,000000047.0000,000000000.7500,000000005.7500,CI,000000202.6875,LB,000000000.6000,000000000.6000,20705032020722,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975284
PRUM,504608X           ,CC ,C1,504608X           , N ,Y,Y,2 ,CK,BX,FOX                                     ,00000005,EA,00000005,20141120 00:00:00,                 ,N,Y,Y,CK ABCDEF ,IN,000000010.2500,000000003.5000,000000004.2500,CI,000000152.4688,LB,000000000.5500,000000000.5500,20705032010150,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975172
PRUM,504608X           ,CC ,C1,504608X           , N ,Y,Y,2 ,CK,BX,FOX                                     ,00000005,EA,00000005,20151120 00:00:00,                 ,N,Y,Y,CK ABCDEF ,IN,000000010.2500,000000003.5000,000000004.2500,CI,000000152.4688,LB,000000000.5500,000000000.5500,20705032010150,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975172
PRUM,67016800          ,CC ,C1,67016800          , U ,Y,Y,2 ,CK,BX,FOX                                     ,00000001,EA,00000001,20141120 00:00:00,               ,N,Y,Y,CK ABCDEF ,IN,000000047.0000,000000000.7500,000000005.7500,CI,000000202.6875,LB,000000000.6000,000000000.6000,20705032020722,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975284

Code is as follows:
Code:
awk -F, -vDATE=`date +%Y%m%d` '{A=$16;gsub(/[[:space:]].*/,X,$16);if($16 <= DATE && $16 != ""){next} else {$16=A;print $0}}' OFS=, file347

Output will be as follows.
Code:
PRUM,504608X           ,CC ,C1,504608X           , N ,Y,Y,2 ,CK,BX,FOX                                     ,00000005,EA,00000005,20151120 00:00:00,                 ,N,Y,Y,CK ABCDEF ,IN,000000010.2500,000000003.5000,000000004.2500,CI,000000152.4688,LB,000000000.5500,000000000.5500,20705032010150,                   ,A,                 ,20141120 00:00:00,   ,GS1 128   ,1.0000,                              ,09975172

Thanks,
R. Singh
Last edited by RavinderSingh13; 11-25-2014 at 03:27 AM.. Reason: A minor change in input file
# 5  
Old 11-25-2014
Try

Code:
$ awk  -F, '{c=$16; gsub(/[: ]/,"",c)} c > sys' sys="$(date +'%Y%m%d%H%M%S')" infile

Code:
$ awk -F, 'BEGIN{sys = strftime("%Y%m%d%H%M%S")}{c=$16; gsub(/[: ]/,"",c)} c > sys'  infile

# 6  
Old 11-25-2014
Quote:
Originally Posted by Akshay Hegde
Try

Code:
$ awk  -F, '{c=$16; gsub(/[: ]/,"",c)} c > sys' sys="$(date +'%Y%m%d%H%M%S')" infile

Code:
$ awk -F, 'BEGIN{sys = strftime("%Y%m%d%H%M%S")}{c=$16; gsub(/[: ]/,"",c)} c > sys'  infile

Since the date data in the file is in the correct order (most significant data comes first in the string) and the fields (year, month, day, hour, minute, and second) are all fixed length fields, the gsub()calls can be avoided if the system date is formatted the same way the dates are formatted in the file:
Code:
awk -F, '$16 > sys' sys="$(date +'%Y%m%d %H:%M:%S')" infile

or, if the GNU time extensions are in your version of awk,
Code:
awk -F, 'BEGIN{sys = strftime("%Y%m%d %H:%M:%S")} $16 > sys' infile

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Filter tab file based on column value

Hello I have a tab text file with many columns and have to filter rows ONLY if column 22 has the value of '0', '1', '2' or '3' (out of 0-5). If Column 22 has value '0','1', '2' or '3' (highlighted below), then remove anything less than 10 and greater 100 (based on column 5) AND remove anything... (1 Reply)
Discussion started by: nans
1 Replies

2. Shell Programming and Scripting

Filter Data based on codes

Hello, I have a question on how to filter the data on multiple columns. The problem is I have one table with 25 columns, 4500 rows. I need to filter out the data based on some codes like 'XXXX', I have 25 codes to check on that table. My requirement is that which ever row has this code I... (1 Reply)
Discussion started by: sandeep309
1 Replies

3. Shell Programming and Scripting

Filter records based on 2nd file

Hello, I want to filter records of a file if they fall in range associated with a second file. First the chr number (2nd col of 1st file and 1st col of 2nd file) needs to be matched. Then if the 3rd col of the first file falls within any of the ranges specified by the 2nd and 3rd cols , then... (4 Replies)
Discussion started by: ritakadm
4 Replies

4. Shell Programming and Scripting

awk filter based on column value (variable value)

Hi, I have a requirement to display/write the 3rd column from a file based on the value in the column 3. Ex: Data in the File (comma delimited) ID,Value,Description 1,A,Active 1,I,Inactive 2,S,Started 1,N,None 2,C,Completed 2,F,Failed I need to first get a list of all Unique IDs in... (7 Replies)
Discussion started by: kiranredz
7 Replies

5. Linux

filter data

hi all, here is my question. i have the following data Block 1 a b c d e f g h i Block 2 j k l m n o p q r i need to grep all information from block 1 i.e 'a to i' (6 Replies)
Discussion started by: yprudent
6 Replies

6. 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

7. Shell Programming and Scripting

copy lines from log files based on timestamp and sysdate

I'm looking for a command or simple script that will read lots of audit log file (*.aud) in log fold every 10 minutes, and will output to one file based on sysdate - 10 minutes. assume the script is run at 11:12:20, and it should grep the line from Wed Jun 17 11:02:43 2009 to end of file. after... (4 Replies)
Discussion started by: percvs88
4 Replies

8. Shell Programming and Scripting

copy lines from log files based on timestamp and sysdate

I am sorry to repost this question. it was not clear, and I had the meeting and didn't response the question on time. I do really need help and appreciate your help very much. I'm looking for a simple shell script that will read lots of audit log file (*.aud) in a log fold every 10 minutes,... (1 Reply)
Discussion started by: percvs88
1 Replies

9. Shell Programming and Scripting

extract data from a data matrix with filter criteria

Here is what old matrix look like, IDs X1 X2 Y1 Y2 10914061 -0.364613333 -0.362922333 0.001691 -0.450094667 10855062 0.845956333 0.860396667 0.014440333 1.483899333... (7 Replies)
Discussion started by: ssshen
7 Replies

10. Shell Programming and Scripting

filter based on column value

I have a file with colon separated values.. the sample is attached below. No of fields in each record/line is dependent on the value of field53. What I need to do is to design a special filter based on specific requirement of some match of values in particular column or combination of columns. ... (2 Replies)
Discussion started by: rraajjiibb
2 Replies
Login or Register to Ask a Question