Shift of dates in a CSV file via bash script

Question

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

Answer 1

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.

Answer 2

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

Answer 3

$ 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

Answer 4

$ 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

Answer 5

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