Bash script/awk to input and output a CSV file

Question

I'm having some trouble creating a bash/awk/sed script that would take a comma-separated CSV file of three columns (firstname, lastname, date of birth), and outputs another CSV file that has the same columns from input with an additional column that shows the difference between the current date and the date of birth in years.

$ yourscript <input CSV file> <output CSV file>

• input.csv may look like this:

  bob,wag,06/13/1958
  ashley,hay,01/23/1983
  evan,bert,09/11/1972
  

• output.csv should look like this:

  bob,wag,06/13/1958,62
  ashley,hay,01/23/1983,37
  evan,bert,09/11/1972,48
  

Answer 1

$ cat data
bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972

To output in a file named output-file and display to STDOUT at the same time:

$ awk -v year="$(\date +%Y)" 'BEGIN{FS="/"} {print $0 "," year-$3}' data | tee output-file
bob,wag,06/13/1958,62
ashley,hay,01/23/1983,37
evan,bert,09/11/1972,48

Or to just output to the same file:

$ awk -v year="$(\date +%Y)" 'BEGIN{FS="/"} {print $0 "," year-$3}' data > output-file

Answer 2

To perform more accurate time computations, you can use gawk's time and string functions (per @AdminBee's suggestion). Using input data as:

$ cat data
bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972

You can get a time difference in days between now and the date shown on each line, with:

$ awk -F, 'BEGIN{today=systime()} 
           {print $0 "," int((today-mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00))/(3600*24))}' \
           data | tee output-file

 bob,wag,06/13/1958,22755
 ashley,hay,01/23/1983,13765
 evan,bert,09/11/1972,17551

The snippet:

int((today-mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00))/(3600*24))

does three basic things for each line of the input file:

• it calculates the time elapsed (in seconds) since 1970-01-01 00:00:00 UTC (on POSIX systems), not counting leap seconds, with mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00)
• it calculates the time difference between the above quantity and the variable today, which contains the number of seconds elapsed at time of execution since 1970-01-01 00:00:00 UTC.
• it divides the time difference in seconds by 3600*24 to get the same in days, and only consider the integer part of the result, to get whole days with int().

You could play with that to get your time difference in seconds, minutes, hours per your need. HTH

Answer 3

To get the date difference, you can use this small bash function

Use following numbers for your needs

• #To get Days difference: 86400
• #To get Years difference: 31536000

---

Bash Function

datediff() {
  current_date=$(date -d "$1" +%s)
  birth_date=$(date -d "$2" +%s)
  echo $(( (current_date - birth_date) / 31536000)) Years
}

Usage:

datediff '9/28/2020' '1/1/1999'

Output:

 21 years

Answer 4

Using Raku (formerly known as Perl_6)

perl6 -MText::CSV -e 'my @a = csv( in => $*IN ); my $date = Date.today;  \
       @a>>.[2] = @a.map: *.[2].subst(/ (\d**2) \/ (\d**2) \/ (\d**4) /, {"$2-$0-$1"} ).Date;  \
       @a>>.[3] = @a.map: (($date - *.[2])/365.2425).floor;  \
       csv( in => @a, out => $*OUT );' < file.txt

Sample Input (file.txt):

bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972
maggie,simpson,05/09/2021
lisa,simpson,05/09/2014
bart,simpson,05/09/2012

Sample Output:

bob,wag,1958-06-13,63
ashley,hay,1983-01-23,39
evan,bert,1972-09-11,49
maggie,simpson,2021-05-09,1
lisa,simpson,2014-05-09,8
bart,simpson,2012-05-09,10

The code above returns ISO 8601 dates, and uses ISO 8601 dates to compute years (floored). Briefly, the .csv file is read in and parsed by the csv(…) function, made available by loading the Text::CSV module at the command line. The data is read into array @a, and today's date is recorded as $date.

In the second line of code, subst is used to analyze the third column and return the date-of-birth in yyyy-mm-dd format, which is then converted to an ISO 8601 date by calling .Date on that string. In the third line of code, each ISO 8601 date-of-birth is subtracted from $date today's date, floored, and returned as an extra column. In the last line of code, valid .csv is output.

If mm/dd/yyyy dates are required in the output (same as input but NOT ISO 8601), then reformat the ISO 8601 Date with the following command, just before the csv(in => @a, out => $*OUT) last command:

@a>>.[2] = @a.map: *.[2].mm-dd-yyyy("/");

---

NOTE: "age-at-date" column is only approximate. The ISO 8601 dates when subtracted will return accurate days, but these are inaccurately converted to years by dividing by 365.2425 days/year and flooring.

[The right way to calculate "age-at-date" is to calculate the number of leap-days a person has lived, e.g. using Raku's is-leap-year function. It's not clear any answer yet posted has accurately addressed this issue--including mine].

https://en.wikipedia.org/wiki/ISO_8601
https://docs.raku.org/language/temporal#index-entry-Date_and_time_functions
https://raku.org

Answer 5

Depending on what you really want:

years difference:

(echo "firstname,lastname,d"; cat input.csv) | csv-sqlite \
 "select *, strftime('%Y', 'now') - substr(d, 7, 4) as year_diff
  from input" | csv-header --remove

age:

(echo "firstname,lastname,d"; cat input.csv) | csv-sqlite
  "select *, strftime('%Y', 'now') - substr(d, 7, 4) -
             case when strftime('%m%d', 'now') >= (substr(d, 1, 2) || substr(d, 4, 2))
             then 0 else 1 end as age
  from input" | csv-header --remove

csv-sqlite and csv-header are from csv-nix-tools