0

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.

  • 1
    What have you tried so far? As you mention awk, what is your current solution and where is your problem to continue on your own? – Stefan M Feb 28 '18 at 16:01
1

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
2

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
  • When I tried this, I got the following output: 1111,2222,3333,4444,5555,6666,7777,1/27/2017/11/27 18:58:48,1/27/2017/11/27 19:07:57 – GustavMahler Feb 28 '18 at 18:22
  • @GustavMahler, you made it wrong – RomanPerekhrest Feb 28 '18 at 20:54
1

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'

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.