5

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

2 Answers 2

6

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}'
12
  • 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. Feb 27, 2018 at 19:52
  • @user1404316 What GNU extension? As far as I know, that code is POSIX. Feb 27, 2018 at 20:01
  • @user1404316 we still need to get rid of the AMs Feb 27, 2018 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. Feb 27, 2018 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. Feb 28, 2018 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"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .