CODEWITHSUNDEEP
shellunixscripting
B.Shiva
B.Shiva

Reputation: 45

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

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.

Upvotes: 0

Views: 83

Answers (3)

Mauro Lacy
Mauro Lacy

Reputation: 389

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

Upvotes: 0

David C. Rankin
David C. Rankin

Reputation: 84541

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

Upvotes: 1

VIPIN KUMAR
VIPIN KUMAR

Reputation: 3137

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

Upvotes: 2

Related Questions