Editing column in csv file depending on its columns using unix shell scripting

Question

I have the following table is in csv format

07-04-2017 , YES
08-04-2017 ,YES
09-04-2017 , YES
10-04-2017 , YES
11-04-2017 , YES
07-04-2017 , YES

I want the first column to be compared with the current date of the system and if the date mentioned in the first column is in the past, then I want to change the 2nd column to "NO". The resultant table is:-

07-04-2017 ,NO
08-04-2017 ,NO
09-04-2017 ,YES
10-04-2017 ,YES
11-04-2017 ,YES
07-04-2017 ,NO

I tried awk statement but it is not comparing dates.Kindly share the command if you know how to go about this problem.

where's the comma in comma-separated-value format? and please add whatever awk command you tried — Sundeep
– Sundeep, Commented Apr 9, 2017 at 15:42
date=$(date '+%m/%d/%Y'); awk -F "," '{ if($2<date) {print "YES"} else {print "NO"}}' But this is returning only "NO" — B.Shiva
– B.Shiva, Commented Apr 10, 2017 at 5:32

VIPIN KUMAR · Accepted Answer · 2017-04-10 06:23:06Z

2

Try this -

$awk -v date=$(date '+%d-%m-%Y') '{print ($1 < date?$1 FS "NO": $1 FS "YES")}' f
07-04-2017 NO
08-04-2017 NO
09-04-2017 YES
10-04-2017 YES
11-04-2017 YES
07-04-2017 NO

As per the comment - Todays's result for this command -

$awk -v date=$(date '+%d-%m-%Y') '{print ($1 < date?$1 FS "NO": $1 FS "YES")}' f
07-04-2017 NO
08-04-2017 NO
09-04-2017 NO
10-04-2017 YES
11-04-2017 YES
07-04-2017 NO

edited Apr 10, 2017 at 6:23

answered Apr 9, 2017 at 16:57

VIPIN KUMAR

3,1572 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

B.Shiva Over a year ago

It is returning only "YES"

VIPIN KUMAR Over a year ago

@BShiva - It is working fine, check my added result.

VIPIN KUMAR Over a year ago

if you are only getting "YES" means $1 is always less than 07-04-2017 possibilities are high that date is wrong in your machine, paste the output of date '+%d-%m-%Y' command.

B.Shiva Over a year ago

I had tried applying it in a table which had the date in the second column and had changed $1 to $2. But after separating the column the proper result came. Thank You.

VIPIN KUMAR Over a year ago

Good to hear that it solved your Query :) stackoverflow.com/help/someone-answers

David C. Rankin · Accepted Answer · 2017-04-10 07:10:04Z

If I understand your question correctly, then there is no need to rely on any other utility. You can make use of POSIX shell parameter expansion and substring removal to parse the year, month, and day and put them together in a reformatted date string that can be used with date -d "string" to get the number of seconds since epoch (1970-01-01 00:00:00 UTC) and compare that against the current date/time to determine if the date read is before the current. You can then output "NO" if it is.

Example:

#!/bin/sh

while IFS=$' \t\n,' read -r fdate ans
do
    yr="${fdate##*-}"    ## parse year
    tmp="${fdate%-$yr}"  ## parse tmp="day-month"
    day="${tmp%-*}"      ## parse day
    mo="${tmp#*-}"       ## parse month
    rfdate="${yr}-${mo}-${day}"   ## create "year-mo-day" string

    ## compare date string with current day/time (seconds since epoch)
    if [ "$(date -d "$rfdate" +%s)" -lt "$(date +%s)" ]
    then
        echo "$fdate, NO"
    else
        echo "$fdate, $ans"
    fi
done < dat/dates

Example Input

$ cat dat/dates
07-04-2017 ,YES
08-04-2017 ,YES
09-04-2017 ,YES
10-04-2017 ,YES
11-04-2017 ,YES
07-04-2017 ,YES

Example Use/Output

$ sh datebeforetst.sh <dat/dates
07-04-2017, NO
08-04-2017, NO
09-04-2017, NO
10-04-2017, NO
11-04-2017, YES
07-04-2017, NO

You can adjust how the current day is compared if you need to consider 'today' not in the past, for example (now - 24 hours):

if [ "$(date -d "$rfdate" +%s)" -lt "$(($(date +%s)-86400))" ]

results in output of:

$ sh datebeforetst.sh <dat/dates
07-04-2017, NO
08-04-2017, NO
09-04-2017, NO
10-04-2017, YES
11-04-2017, YES
07-04-2017, NO

Mauro Lacy · Accepted Answer · 2017-04-17 12:37:01Z

0

Here's a bash version

#!/bin/bash

DATE=`date +%d-%m-%Y`
while read L
do
    D=`echo $L | cut -f1 -d\ `
    if [ $D \< $DATE ]
    then
        echo "$D NO"
    else
        echo "$D YES"
    fi
done <./dates.txt

All in one line:

DATE=`date +%d-%m-%Y`; while read L; do D=`echo $L | cut -f1 -d\ `;[ $D \< $DATE ] && echo "$D NO" || echo "$D YES"; done <./dates.txt

edited Apr 17, 2017 at 12:37

answered Apr 9, 2017 at 15:52

Mauro Lacy

3892 silver badges10 bronze badges

2 Comments

B.Shiva Over a year ago

The above code is returning only "YES" if I write it in one line. In script it gives this error:: length.ksh[5]: [: <: unknown operator

Mauro Lacy Over a year ago

It's probably because you are using ksh. Use bash instead. I'll write a ksh version later when I have time.

Collectives™ on Stack Overflow

Editing column in csv file depending on its columns using unix shell scripting

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related