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

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