I am working with a csv file which contains data in the following structure:
"12345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333,"15/12/2017 4:26:00 PM"
I want to convert the 12 hour time into 24 hour time.
The following shows what I am trying to achieve in the end:
"12345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333,"15/12/2017 16:26:00"
I found the following answer to a question which seems to solve the conversion of the time segment of my problem.
https://stackoverflow.com/questions/8083973/bash-and-awk-converting-a-field-from-12-hour-to-24-hour-clock-time#8084087
So with the above, I believe I must do the following process (There is probably a more efficient method):
-
Temporarily separate the date and time into there own records
"12345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333,"15/12/2017","4:26:00 PM"
- Target the time record and convert it into my desired 24 hour format
- Concatenate the date and time records back into a single record
I am trying to achieve this using awk and am stuck on the first section! Is awk to right tool for this job, or would you recommend a different tool?
I'm starting with step 1. I can't even successfully target the date!
awk 'BEGIN {FS=","} { gsub(/[0-9]\{2\}\/[0-9]\{2\}\/[0-9]\{4\}/, "TESTING"); print }' myfile.csv
Best Answer
I'd use
perl
here:That is add 12 to the hour part if PM (except for 12PM) and change 12AM to 0.
With
awk
, not doing the word-boundary part (so could give false positives on123:21:99 AMERICA
for instance) and assuming there's only one occurrence per line: