Visit The New, Modern Unix Linux Community


delete a row in csv file based on the date


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting delete a row in csv file based on the date
# 1  
delete a row in csv file based on the date

Hi,
I have a csv file with old data..i need to have only last 30 days from the current dateof data in the file.The fourth field in the file is a date field.i need to write a script to delete the old data by comparing the the fourth field with the (current date -30).I need to delete the rows in red as they are very old in the month of feburary.Please Could you kindly let me know how to acheive this in unix scripting.

sample records
----------------------
24,126,1,19/02/2011 03:44:15,1
24,126,2,19/02/2011 09:17:06,1

30,126,38,30/07/2011 13:06:35,1
30,126,39,30/07/2011 13:07:12,1

727,19,111,12/08/2011 15:43,1
727,126,94,12/08/2011 15:43,1

24,126,2,19/02/2011 09:17:06,1
# 2  
If you have gawk or nawk:

Code:
gawk -v FS="," 'BEGIN { REF=systime() - (30 * 24 * 60 * 60); }
{
        # Split DD/MM/YYYY HH:MM:SS into DD MM YYYY HH MM SS
        split($4, a, " ");
        split(a[1], da, "/");
        split(a[2], ta, ":");
        # add seconds if missing
        if(!ta[3]) ta[3]="00";
        # paste into YYYY MM DD HH MM SS and make timestamp
        t=mktime(da[3] " " da[2] " " da[1] " " ta[1] " " ta[2] " " ta[3]);
# Only print when t is newer than 30 days ago
}       (t>REF)' < input > output

# 3  
In perl...
Code:
#!/usr/bin/perl

use Time::Local;
use Time::localtime;

$today = time();
$range = 30*24*60*60;

while (<ARGV>) {
    @f = split /,/, $_;
    @dt = split /[ :\/]/, $f[3];

    $dy = $dt[0] ? $dt[0] : 0;
    $mo = $dt[1] ? $dt[1] - 1 : 0;
    $yr = $dt[2] ? $dt[2] - 1900 : 0;
    $hr = $dt[3] ? $dt[3] : 0;
    $mi = $dt[4] ? $dt[4] : 0;
    $se = $dt[5] ? $dt[5] : 0;

    $sfe = timelocal($se,$mi,$hr,$dy,$mo,$yr);
    print if ($today - $sfe < $range);
}

Save the above perl script in a file and run it as...
Code:
perl_script input_file


Last edited by shamrock; 08-19-2011 at 05:09 PM.. Reason: typos

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #20
Difficulty: Medium
India's first Super Computer, the PARAM 8000, was installed in 1991.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Select and copy .csv files based on row and column number

Dear UNIX experts, I'm a command line novice working on a Macintosh computer (Bash shell) and have neither found advice that is pertinent to my problem on the internet nor in this forum. I have hundreds of .csv files in a directory. Now I would like to copy the subset of files that contains... (8 Replies)
Discussion started by: rcsapo
8 Replies

2. UNIX for Beginners Questions & Answers

How to delete row in csv file on date range?

I want to delete row in csv file , which row value from 2009-10-01 to 2011-06-03 using script.my csv row data look like: 2009-10-01 2011-03-30 2011-03-31 2011-04-01 2011-06-03 2011-06-30 2011-07-01 2011-09-28 ... (7 Replies)
Discussion started by: rakibul639
7 Replies

3. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

4. Shell Programming and Scripting

Bash - delete from csv all the row if the first column is length >

Hi guys, i have a csv file like: USERID;COG;DESCR;FIL;OFF user001;user;test1;001;A01 user002;user;test2;002;A02 user0003;user;test3;003;A03 user004;user;test4;004;A04 user0005;user;test5;005;A05 etc.. I need to read line for line and, if value of first column is > 7 char (in this example... (4 Replies)
Discussion started by: kamose
4 Replies

5. Shell Programming and Scripting

Delete duplicate row based on criteria

Hi, I have an input file as shown below: 20140102;13:30;FR-AUD-LIBOR-1W;2.495 20140103;13:30;FR-AUD-LIBOR-1W;2.475 20140106;13:30;FR-AUD-LIBOR-1W;2.495 20140107;13:30;FR-AUD-LIBOR-1W;2.475 20140108;13:30;FR-AUD-LIBOR-1W;2.475 20140109;13:30;FR-AUD-LIBOR-1W;2.475... (2 Replies)
Discussion started by: shash
2 Replies

6. UNIX for Dummies Questions & Answers

using sed delete a line from csv file based on specific data in two separate fields

Hello, :wall: I have a 12 column csv file. I wish to delete the entire line if column 7 = hello and column 12 = goodbye. I have tried everything that I can find in all of my ref books. I know this does not work /^*,*,*,*,*,*,"hello",*,*,*,*,"goodbye"/d Any ideas? Thanks Please... (2 Replies)
Discussion started by: Chris Eagleson
2 Replies

7. UNIX for Dummies Questions & Answers

Delete a row from a file if one column containing a date is greater than the current system date

Hello gurus, I am hoping someone can help me with the required code/script to make this work. I have the following file with records starting at line 4: NETW~US60~000000000013220694~002~~IT~USD~2.24~20110201~99991231~01~01~20101104~... (4 Replies)
Discussion started by: chumsky
4 Replies

8. Shell Programming and Scripting

How to Delete Last Row from .csv file in perl

Hi , I've a perl script to convert .xls to .csv .After conversion I want to delete first 28 and the last row from .csv file.Is there any efficent way to achive this both together. I'm deleting first 28 rows by using folllowing my perl code: exec " tail -n+28 infile.csv > outfile.csv ... (7 Replies)
Discussion started by: ajaypatil_am
7 Replies

9. UNIX for Dummies Questions & Answers

Delete first row of csv file

I have a csv file, which is > 2 Gigs. I need to BCP that file to Sybase db , but I cant upload that b'caz first row of the file is failing. ( having some errors probably.) I can manually insert the first line into db & then I can upload the rest of the data in file, if i can delete the first row. ... (2 Replies)
Discussion started by: kedar.mehta
2 Replies

10. UNIX for Advanced & Expert Users

Delete File Based On Date

Hi Xpert Out There I have a lots of file in this path : -rw-r----- 1 oracle dba 3954176 Dec 21 2006 1_2008.dbf -rw-r----- 1 oracle dba 887808 Dec 21 2006 1_2009.dbf -rw-r----- 1 oracle dba 143872 Dec 21 2006 1_2010.dbf -rw-r----- 1 oracle dba ... (3 Replies)
Discussion started by: adzuanamir
3 Replies

Featured Tech Videos