Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a csv that I need to remove all lines that are older than 7 days. This is the format of the csv

Person ID  VIP  CS  SS  LT  FTLT  PS  Modified Datestamp
T001028    1    1   1   0   0     0   05-07-2013
T001250    1    1   1   0   0     0   08-05-2012
T001261    1    1   1   0   0     1   04-04-2013
T001345    1    1   1   0   0     0   04-03-2013
T078503    1    1   1   0   0     0   04-03-2013
T079819    1    1   1   0   0     1   3/22/2013
T080119    1    1   1   0   0     1   04-02-2013
T090574    1    1   1   0   0     0   11/15/2012
T091106    1    1   1   0   0     1   3/22/2013

The format of the column Modified Date is MM/DD/YYYY - Any Ideas.... be looking to execute something on a RedHat 5 linux box.

share|improve this question
1  
Why do some contain dash separators, and some slashes? What is actually being used? –  Chris Down Jul 5 '13 at 2:34
    
Unfortunatley this is how the csv comes to us, some records with - seperating the date some with / –  Simon Ellis Jul 11 '13 at 21:10
    
Your data looks whitespace-separated, not comma-separated. CSV stands for comma-separated values. –  Kaz Jul 15 at 18:34

5 Answers 5

If you calculate the date up to which the lines shall be ignored before you call awk then you can do this:

awk -v cmpdate=20130628 '{line=$0; dateval=$8;FS="/"; $0=dateval; 
  thisdate=$3*10000+$1*100+$2; if (thisdate>cmpdate) print line; FS=" ";}' file

Edit 1:

Reset FS to its original value at the end. I tested my code with just one line of input so that it didn't make a difference...

share|improve this answer
    
That's some neckbeard vomit right there. Nice. –  Mel Boyce Jul 5 '13 at 12:52
1  
@MelBoyce what is "neckbeard vomit"? It doesn't sound very complimentary. –  terdon Jul 5 '13 at 13:04
    
Oh, it's very complimentary. We should all aspire to that level of tool voodoo. –  Mel Boyce Jul 5 '13 at 16:33
    
@MelBoyce is that your term or have you heard it used in a positive way before? Have a look at my question here. –  terdon Jul 5 '13 at 18:17
1  
@terdon - The accepted answer on english.stackexchange.com is pretty much right. A neckbeard (at least in the context used by my peers) is an older, wiser UNIX guru (usually someone who started using UNIX back in the sixties or seventies) and the context for "vomit" in this case refers to the unreadability, but succinctness of the answer. –  Mel Boyce Jul 11 '13 at 3:46

Here's a method using tail, date -d ..., awk, and just the functionality of Bash.

tail -n+2 file.csv | {
  while read line ; do
    tmstmp=$(echo "$line" | awk '{print $8}');
    [ $(( $(date -d "now" +%s) - $(date -d "$tmstmp" +%s) )) -lt $(( 60*60*24*7 )) ] && echo "$line";
  done;
}

How it works?

The above parses the lines from a file, file.csv, and get's the 8th column (the date), and then calculates the delta between the number of seconds since epoch for now and the parsed date. If they're less then 7 days worth of seconds then the line get's printed.

Debugging

You can put this line in to debug what's going on. Put it just after the tmpstmp=... line:

echo "TMSTMP: $tmstmp" "TMDELTA: $(( $(date -d "now" +%s) - $(date -d "$tmstmp" +%s) ))" "TMWINDOW: $(( 60*60*24*7 ))"

Example

For simplicity I put the above into a script and called it rprttime.bash:

#!/bin/bash

tail -n+2 file.csv | {
  while read line ; do
    tmstmp=$(echo "$line" | awk '{print $8}');
    echo "TMSTMP: $tmstmp" "TMDELTA: $(( $(date -d "now" +%s) - $(date -d "$tmstmp" +%s) ))" "TMWINDOW: $(( 60*60*24*7 ))"
    [ $(( $(date -d "now" +%s) - $(date -d "$tmstmp" +%s) )) -lt $(( 60*60*24*7 )) ] && echo "$line";
  done;
}

Now when we run it:

$ ./rprttime.bash
TMSTMP: 05/07/2013 TMDELTA: 5157421 TMWINDOW: 604800
TMSTMP: 08/05/2012 TMDELTA: 28917421 TMWINDOW: 604800
TMSTMP: 04/04/2013 TMDELTA: 8008621 TMWINDOW: 604800
TMSTMP: 04/03/2013 TMDELTA: 8095021 TMWINDOW: 604800
TMSTMP: 04/03/2013 TMDELTA: 8095021 TMWINDOW: 604800
TMSTMP: 3/22/2013 TMDELTA: 9131821 TMWINDOW: 604800
TMSTMP: 04/02/2013 TMDELTA: 8181421 TMWINDOW: 604800
TMSTMP: 11/15/2012 TMDELTA: 20101021 TMWINDOW: 604800
TMSTMP: 3/22/2013 TMDELTA: 9131821 TMWINDOW: 604800

If you change the window of time from 7 days to say 60 days and disable the debugging line, you'll see some of the lines getting printed:

$ date
Fri Jul  5 16:49:19 EDT 2013

$ ./rprttime.bash
T001028    1    1   1   0   0     0   05/07/2013
share|improve this answer

As there seem not to be a better answer than this (all purposed solution will do one fork to /bin/date for each lines), there is a clean and efficient way to do the job,but using perl.

Question stand for csv (coma separated value) and present tsv (tab sep vals), so my script will work for any kind of tab, coma or space separated values (just look for last field).

No fork, perl will do the date parsing himself:

#!/usr/bin/perl -w

use strict;
use Date::Parse;

my $day=7;
if ($ARGV[0] && $ARGV[0] > 0) { $day=$ARGV[0]; shift; };

while (<>) {
    my @fields=split(/[ \t,]/);
    print if /^Person/ || str2time($fields[$#fields]) > time()-$day*86400;
};

U could run this without argument, as a filter or with filename as argument:

./dropOlderThan.pl < file.tsv
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp

./dropOlderThan.pl file.tsv
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp

If first argument is a number, they will be interpreted as number of day to keep in output:

./dropOlderThan.pl 144 file.tsv
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp
T001028     1   1   1   0   0   0   05-07-2013
T001261     1   1   1   0   0   1   04-04-2013
T001345     1   1   1   0   0   0   04-03-2013
T078503     1   1   1   0   0   0   04-03-2013

And finally, if you want to modify the file in place:

perl -i dropOlderThan.pl 144 file.tsv 
cat file.tsv 
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp
T001028     1   1   1   0   0   0   05-07-2013
T001261     1   1   1   0   0   1   04-04-2013
T001345     1   1   1   0   0   0   04-03-2013
T078503     1   1   1   0   0   0   04-03-2013
share|improve this answer

Yet another answer with limited forks

As there is a lot of forks to whipe out, there is a way for doing this using sed and only 1 fork to /bin/date:

sedstr=""
{
    i=1;
    read now;
    while read line;do
        ((i++));
        [ $(( (now-line) /86400 )) -gt 143 ] && sedstr="${i}d;$sedstr"
      done
}< <(
    sed -ne $'s/^.*[ \t,]//g;y|-|/|;/[0-9]$/p;1inow' < file.tsv |
        date -f - +%s
)
sed -e "$sedstr" < file.tsv
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp
T001028     1   1   1   0   0   0   05-07-2013
T001261     1   1   1   0   0   1   04-04-2013
T001345     1   1   1   0   0   0   04-03-2013
T078503     1   1   1   0   0   0   04-03-2013

The last sed command could be used with -i for inplace modification instead of output to console.

echo $sedstr 
10d;9d;8d;7d;3d;

sed -e $sedstr -i file.tsv 
cat file.tsv 
Person ID   VIP CS  SS  LT  FTLT    PS  Modified Datestamp
T001028     1   1   1   0   0   0   05-07-2013
T001261     1   1   1   0   0   1   04-04-2013
T001345     1   1   1   0   0   0   04-03-2013
T078503     1   1   1   0   0   0   04-03-2013
share|improve this answer

I would do this with Perl (run this from the terminal) :

$ perl -lane 'BEGIN{$date=`date +%s`; chomp($date)}    
            if($.==1){print}                         
            else{
                 $F[$#F]=~s/-/\//g; 
                 $fdate=`date -d "$F[$#F]" +%s`;     
                 chomp($fdate);                      
            print if $date-$fdate<604800;     
          }' file.csv

This script works by calculating today's date in seconds since the epoch, then translating the date of each line to the same format, subtracting it from today's date and printing only if it is less than 7 days old (7*24*60*60=604800).

NOTES

  • The script works with seconds, this may be a bit too precise for what you had in mind. Let me know if so, and I will modify it to work at the level of days instead.

  • I am also converting MM-DD-YYYY to MM/DD/YYYYon the fly, this may not be necessary if your input file is homogeneously formatted but it is needed for the example you have posted.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.