0

Currently I have a file with records like this :

D20211011,S0519,306668,1
D20211004,S1600,306668,1
D20211009,S1604,306668,1
D20211010,S1605,306668,1
D20211006,S1610,306668,1
D20211011,S1611,306668,1

Assuming that the current date is 20211011, I need to apply a transformation on the rows only where date is less than the current date and that row with date in the past should be updated to current date.

In the example shared above, transformation should be done on rows 2 to 5.

D20211004,S1600,306668,1 -> D20211011,S1600,306668,1
D20211009,S1604,306668,1 -> D20211011,S1604,306668,1
D20211010,S1605,306668,1 -> D20211011,S1605,306668,1
D20211006,S1610,306668,1 -> D20211011,S1610,306668,1
2
  • 5
    Please edit your post to indicate what you already tried and where you ran into problems. That way contributors can understand what tools you have available/are familiar with, and you can avoid receiving proposed solutions that you already know won't work.
    – AdminBee
    Oct 12 at 12:43
  • 3
    Can't you just replace the first field with the current date always? Or do you also expect there be dates in the future or lines that don't start with a DYYYYMMDD? Oct 12 at 16:55
5

In your case, you have the advantage that the dates are given in ISO style, meaning they can be interpreted as integer values and simply compared using arithmetic operators (<, = and >) while still yielding the correct order.

So, you can use the following awk program:

awk -v cur="20211011" 'BEGIN{FS=OFS=","} {ldate=substr($1,2); if (ldate<cur) $1="D" cur} 1' input.csv

The current date is defined as awk variable cur. At the beginning, the field separator for input and output is set to ,. Then, for every line, the line date is determined by stripping the first character from field 1 of the line. If the resulting "integer" is less than cur, the field is overwritten by the concatenation of D and the content of cur. The seemingly "stray" 1 outside of the rule block instructs awk to print the current line, including any possible modifications.

4

Try awk:

awk -v today=$(date +%Y%m%d) '
    BEGIN{FS=OFS=","}
    substr($1,2)<today{$1="D"today;}
1' file
  • -v today=$(date +%Y%m%d) Set a variable with the current date.
  • BEGIN{FS=OFS=","} Sets input (FS) and output (OFS) field delimiter.
  • substr($1,2)<today Cut off D from first field and compare it to current date.
  • $1="D"today; Replace first field with current date
  • 1 Evaluates always to true and thus prints the line
2
  • I have just one question around this.. When i tried to do the same statement but instead of defining today in the awk itself, i defined it globally in my program something like this awk -v $today ' BEGIN{FS=OFS=","} substr($1,2)<$today{$1="D"$today;) 1' file This gave me an error awk: 20211007' argument to -v' not in `var=value' form Oct 13 at 6:55
  • 2
    Don't get it. What should -v $today do? Variables other than field values 0..n don't start with $. And you assign variables in the form name=value.
    – pLumo
    Oct 13 at 7:14
2
$ awk -v d='D20211011' 'BEGIN{FS=OFS=","} $1<d{$1=d} 1' file
D20211011,S0519,306668,1
D20211004,S1600,306668,1
D20211009,S1604,306668,1
D20211010,S1605,306668,1
D20211006,S1610,306668,1
D20211011,S1611,306668,1

$ awk -v d="$(date +'D%Y%m%d')" 'BEGIN{FS=OFS=","} $1<d{$1=d} 1' file
D20211012,S0519,306668,1
D20211012,S1600,306668,1
D20211012,S1604,306668,1
D20211012,S1605,306668,1
D20211012,S1610,306668,1
D20211012,S1611,306668,1
1
$ perl -sF, -pale 's/.*?,/$d,/ if $F[0] lt $d' -- -d="D20211011" ./file
D20211011,S0519,306668,1
D20211011,S1600,306668,1
D20211011,S1604,306668,1
D20211011,S1605,306668,1
D20211011,S1610,306668,1
D20211011,S1611,306668,1

GNU sed running in extended regex mode can also achieve the desired output. Date comparison is done by looking at the first non matching MSD in the two dates.

n=$(printf '%d' {0..9})
d=D20211011
sed -En "/\n/ba
  s/.*/$d\n&\n$n/
  /^(.*)(.).*\n\1(.).*\n.*\2.*\3/D
  s/\n[^,]*//;:a;P
" file
0

Using Raku (formerly known as Perl_6)

raku -pe 's/ ^^ D <(\d*?)> \, /20211011/;'

As @StéphaneChazelas notes in a comment to your OP, it is unclear whether "future dates" will ever appear in your first column. If not, then a simple s/// replacement suffices, which is what the Raku code above accomplishes (substituting all date digits it finds regardless of numerical < = > comparison).

If however, you desire to update the first column to a value based on a numerical < = > comparison, you can use the Raku code below, which executes a block containing Raku's ternary operator in the replacement half of the s/// operator:

raku -pe 'my Int $d=20211011; s/ ^^ D (\d*?) \, /D{$0 < $d ?? $d !! $0},/;'

Sample Input:

D20211011,S0519,306668,1
D20211004,S1600,306668,1
D20211009,S1604,306668,1
D20211010,S1605,306668,1
D20211006,S1610,306668,1
D20211011,S1611,306668,1

Sample Output (for either Raku code example, above):

D20211011,S0519,306668,1
D20211011,S1600,306668,1
D20211011,S1604,306668,1
D20211011,S1605,306668,1
D20211011,S1610,306668,1
D20211011,S1611,306668,1

For the second Raku code example, note that the variable $d is Type-constrained to Int as a further check on correctness. And as @AdminBee notes you're lucky that the first column contains ISO dates which can be compared with < = > operators and still give the correct result.

Regarding the (simple) second line of Raku code above, you should note that there's no check on the $0 capture to insure valid dates (e.g. ensuring no 13th month nor 32nd day appears). There isn't even code to exclude incomplete dates (e.g. month/day sans year).

OTOH, adding an approprate date-validation check should be relatively easy, using Raku's built-in support for Date and DateTime objects (no extra modules required; example and link below).

$ echo "2020-02-29" | raku -ne '.Date.raku.say'
Date.new(2020,2,29)

$ echo "2021-02-29" | raku -ne '.Date.raku.say'
Day out of range. Is: 29, should be in 1..28
  in block <unit> at -e line 1

https://docs.raku.org/language/temporal#index-entry-Date_and_time_functions
https://raku.org

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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