Formatting date order in csv using awk

Question

I have the a csv file with the following data stucture: [MM/DD/YYYY]

1111,2222,3333,4444,5555,6666,7777,11/27/2017 18:58:48,11/27/2017 19:07:57

I am aiming to change the format of the date into: [YYYY/MM/DD]

1111,2222,3333,4444,5555,6666,7777,2017/11/27 18:58:48,2017/11/27 19:07:57

I am trying to accomplish this using awk, but any other tools that do the job would also be acceptable.

Answer 1

Awk solution:

awk -F',' 'function reformat_date(c){     # `c` - column value as an argument
               y = substr(c, 7, 4);       # extract `year` value
               sub(/\/[0-9]{4}/, "", c);  # remove `year` value
               return y"/"c               # return rearranged `YYYY/MM/DD` value
           }
           { $8 = reformat_date($8); $9 = reformat_date($9) }1' OFS=',' file.csv

Sample output:

1111,2222,3333,4444,5555,6666,7777,2017/11/27 18:58:48,2017/11/27 19:07:57

Answer 2

Try this :

sed -E 's|([0-9]{2}/[0-9]{2})/([0-9]{4})|\2/\1|g' file

or

perl -pe 's|(\d{2}/\d{2})/(\d{4})|$2/$1|g' file

Output :

1111,2222,3333,4444,5555,6666,7777,2017/11/27 18:58:48,2017/11/27 19:07:57

Answer 3

If your data does not include any other date-like formats, you can use sed on the whole line to change the dates:

sed 's#\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9][0-9][0-9][0-9]\)#\3/\1/\2#g'