Visit Our UNIX and Linux User Community


Removing rows from a file based on date comparison


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Removing rows from a file based on date comparison
# 1  
Old 10-03-2009
Removing rows from a file based on date comparison

I have a '|' delimited file and want to remove all the records from the file if the date is greater than a year from sysdate. The layout of the file is as below -

xxxxxxxxxxxxxx|yyyyyy|zzzzzz|2009-12-27-00:00| 000000000|N
xxxxxxxxxxxxxx|yyyyyy|zzzzzz|2010-01-03-00:00| 000000000|N
xxxxxxxxxxxxxx|yyyyyy|zzzzzz|2010-11-10-00:00| 000000000|N
xxxxxxxxxxxxxx|yyyyyy|zzzzzz|2010-12-17-00:00| 000000000|N

As you can see above, we need to pick the year, month and day from the timestamp field from the file and do the comparison if if it greater than a year from the current date, then delete that record.

From the data given above, the third and fourth records will need to be deleted and the first and the second ones are to be retained.

Any pointers/directions is greatly appreciated..

Thanks,
Max
# 2  
Old 10-03-2009
Code:
SECS_IN_YEAR=-31536000
while read $line
do
  DIFF=`echo $line | awk -F"|" '{ split($4,a,":");print systime() - mktime(a[1]" "a[2]" "a[3]" 00 00 00") }'`
  if [ $DIFF -gt $SECS_IN_YEAR ]
  then
     echo $line >> outputfile
  fi
done < inputfile

# 3  
Old 10-03-2009
Code:
#!/bin/sh
ONEYEARFROMNOW=$(date  -d "now + 1 year" +'%Y%m%d%H%M')
while read line; do
  recorddate=$(echo $line|cut -f4 -d'|'|tr -d ':-' )
  if [ $recorddate -lt $ONEYEARFROMNOW ]; then
    echo "$line"
  fi
done < infile

This is using GNU date
# 4  
Old 10-03-2009
How about:

Code:
awk -F "|" -v d=$(date "+%Y%m%d") '{split($4,a,"-");s=a[1]a[2]a[3]}s-d<10000' file



---------- Post updated at 19:44 ---------- Previous update was at 18:26 ----------

This seems to be more logical, the year is decreased with one:

Code:
awk -F "|" -v d=$(date "+%Y%m%d") '{split($4,a,"-");s=(a[1]-1)a[2]a[3]}s<d' file

# 5  
Old 10-03-2009
Franklin, this works perfect, thanks !!

Thanks All for your quick response.

- Max...

Previous Thread | Next Thread
Test Your Knowledge in Computers #176
Difficulty: Easy
Apple's first product was the Apple I, invented by Apple co-founder Steve Wozniak.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merging two file based on comparison of first columns

Respected Members. Hello. This is my first post in the forum. I will try to follow all the rules as prescribed by the forum. In case of non-compliance, I request you to kindly give me some more time to understand and abide by them. I am working on two files. I wish to merge the two files... (1 Reply)
Discussion started by: manojmalhotra
1 Replies

2. Shell Programming and Scripting

Removing Duplicate Rows in a file

Hello I have a file with contents like this... Part1 Field2 Field3 Field4 (line1) Part2 Field2 Field3 Field4 (line2) Part3 Field2 Field3 Field4 (line3) Part1 Field2 Field3 Field4 (line4) Part4 Field2 Field3 Field4 (line5) Part5 Field2 Field3 Field4 (line6) Part2 Field2 Field3 Field4... (7 Replies)
Discussion started by: ekbaazigar
7 Replies

3. Shell Programming and Scripting

File comparison based on contents

Hi I have 2 files 1.del ---- 1,2,3,4,5 1,2,3,4,4 1,1,1,1,2 2.del ---- 1,2,3,4,5 1, 1,2,3,4,4 1,1,1,1,2 I need to compare the above two files in unix, as in the output should only tell the difference in contents as I should get only the line 1 ( from 2.del) , rest all lines are... (4 Replies)
Discussion started by: Ethen561
4 Replies

4. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

5. Shell Programming and Scripting

removing rows from text file older than certain date

Hi I need a way of removing rows from a txt file that are older than 30 days from today, going by the date in column 2, below is an example from my file. I have tried awk but don't have enough knowledge. I would really appreciate some help. 41982,15/07/2010,H833AB/0,JZ,288... (6 Replies)
Discussion started by: firefox2k2
6 Replies

6. UNIX for Dummies Questions & Answers

Removing duplicate rows & selecting only latest date

Gurus, From a file I need to remove duplicate rows based on the first column data but also we need to consider a date column where we need to keep the latest date (13th column). Ex: Input File: Output File: I know how to take out the duplicates but I couldn't figure out... (5 Replies)
Discussion started by: shash
5 Replies

7. Shell Programming and Scripting

Removing rows based on a different file (ignore my earlier post - there was a mistake).

Sorry I made a mistake in my last post (output is suppose to be the opposite). Here is a revised post. Hi, I am not sure if this has already been asked (I tried the search but the search was too broad). Basically I want to remove rows based on another file. So file1 looks like this (tab... (3 Replies)
Discussion started by: kylle345
3 Replies

8. Shell Programming and Scripting

Removing rows based on a another file

Hi, I am not sure if this has already been asked (I tried the search but the search was too broad). Basically I want to remove rows based on another file. So file1 looks like this (tab seperated): HHN 3 5 5 HUJ 2 2 1 JJJ 3 1 1 JUN 2 1 3 I have another file (file2)... (2 Replies)
Discussion started by: kylle345
2 Replies

9. Shell Programming and Scripting

delete rows in a file based on the rows of another file

I need to delete rows based on the number of lines in a different file, I have a piece of code with me working but when I merge with my C application, it doesnt work. sed '1,'\"`wc -l < /tmp/fileyyyy`\"'d' /tmp/fileA > /tmp/filexxxx Can anyone give me an alternate solution for the above (2 Replies)
Discussion started by: Muthuraj K
2 Replies

10. Shell Programming and Scripting

Removing rows from a file

I have a file like below and want to use awk to solve this problem. The record separator is ">". I want to look at each record section enclosed within ">". Find the row with the 2nd and 3rd columns being 0, such as 10 0 0 I need to take the first number which in this case is 10. Then... (15 Replies)
Discussion started by: kristinu
15 Replies

Featured Tech Videos