4

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):

  1. 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"

  2. Target the time record and convert it into my desired 24 hour format
  3. 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
5

I'd use perl here:

perl -pe 's{\b(\d{1,2})(:\d\d:\d\d) ([AP])M\b}{
  $1 + 12 * (($3 eq "P") - ($1 == 12)) . $2}ge'

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 on 123:21:99 AMERICA for instance) and assuming there's only one occurrence per line:

awk '
  match($0, /[0-9]{1,2}:[0-9]{2}:[0-9]{2} [AP]M/) {
    split(substr($0, RSTART, RLENGTH), parts, /[: ]/)
    if (parts[4] == "PM" && parts[1] != 12) parts[1] += 12
    if (parts[4] == "AM" && parts[1] == 12) parts[1] = 0

    $0 = substr($0, 1, RSTART - 1) \
         parts[1] ":" parts[2] ":" parts[3] \
         substr($0, RSTART + RLENGTH)
  }
  {print}'
  • The OP should be made aware that you are using the GNU extensions to awk, ie gawk, which might not be available on his/her system. Also, you can make small efficiency gains by replacing $0 with $NF, since we know we are dealing with the final field of every line. Finally, instead of having the address range checking for a complicated regex match including [AP]M for every line, and later checking again for PM, just have the address range /PM\"$/, put the match command inside the braces with PM only, and then you can remove the if clause. – user1404316 Feb 27 '18 at 19:52
  • @user1404316 What GNU extension? As far as I know, that code is POSIX. – Stéphane Chazelas Feb 27 '18 at 20:01
  • @user1404316 we still need to get rid of the AMs – Stéphane Chazelas Feb 27 '18 at 20:03
  • 1
    No need to "@" the author of the post you're replying to. The author of the post is always notified. That's why SE doesn't offer it to you as a possible completion. – Stéphane Chazelas Feb 27 '18 at 20:16
  • 1
    @MinMax, my correction is based on the output of date +%r in a British locale. Which use 12:00 am .. 12:59am 1:00am .. 11:59am 12:00pm..12:59pm 1:00pm .. 11:59pm for 00:00 .. 23:59. I think that's the most common way these days. – Stéphane Chazelas Feb 28 '18 at 20:36
0

This solution converts all items of the 12-hour format to the 24-hour format in each line.

gawk -F, '
{
    for(i = 1; i <= NF; i++) {
        if($i ~ /:[0-9]{2} [PA]M/) {
            split($i, arr, ":| ")

            if(arr[5] ~ /P/)
                arr[2] += 12 

            $i = sprintf("%s %02d:%02d:%02d\"", arr[1], arr[2], arr[3], arr[4])
        }
    }
    print
}' OFS=, input.txt

Input

"12345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","15/12/2017 4:26:00 PM"
"22345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","16/12/2017 6:26:00 AM"
"32345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","17/12/2017 10:00:00 PM"
"42345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","18/12/2017 11:26:00 AM"
"52345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","19/12/2017 2:26:00 PM"

Output

"12345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","15/12/2017 16:26:00"
"22345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","16/12/2017 06:26:00"
"32345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","17/12/2017 22:00:00"
"42345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","18/12/2017 11:26:00"
"52345","BLAH","DEDA","0.000","1.111","2.22222","3.3333333","19/12/2017 14:26:00"

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.