Converting 12 Hour Time Formats to 24 Hour Time Format

[DJLunacy]

I hope everybody is having a wonderful time self-isolating and staying safe!

I have some data in a spreadsheet (CSV) file that I need to convert some data because it's destination requires the time codes to be in a 24-hour format for some reason.

To make this simple I can isolate all of the data that needs to be converted for the moment and just do the conversion via KM. (I think this would be the easiest way, I'm open to alternative methods)

I did manage to find some python code that will spit out a converted time, however, I'm not sure the best approach to do this automagically.

So assuming I have the file as timestoconvert.csv

Would it be easier to find some Regex to do this?

The formatting of the times appears as time ranges in each line

ex. 7:00 am - 9:00 pm

This is an example of the python code that works for a one off..

convert.pl (759 Bytes)
Thanks!

[Yu_Cai]

Since I can not go out, I spend everyday with Keyboard Maestro. It's really a good APP
I don't have experience with csv file. But if the line is like “7:00 am - 9:00 pm”.
You can use “search Using RegEx” macro in KM to extract first Time and last Time.

Problem 1: Extracting Time String From the Line
You can use "search using RegEx" macro.
the RegEx should be:(((\d|\d\d):\d\d)( )(am|pm))( - )(((\d|\d\d):\d\d)( )(am|pm))

Problem 2: Convert 12 Hour Time to 24 Hour Time
Refer the page:
https://macscripter.net/viewtopic.php?id=35417
MacScripter / How to convert 12hour format to 24 hour format?

Refer the code on the page, and edit it to fit our case:

Now we got our final KMM. It works good on my machine(KM Version 9.0.4@MacOS10.12.6).
You can use "For each" macro in KM to do the conversion line by line.
Hope it helps.
ANSWER convert 12hour time to 24 hour time v0_Backup_20200326_0955_v0_Backup_20200326_0955_v0.kmmacros (7.5 KB)

[DJLunacy]

Thank you! I will give this ago later tonight. I appreciate the help!

Stay safe!

[JMichaelTX]

@DJLunacy

I have some data in a spreadsheet (CSV) file that I need to convert some data because it's destination requires the time codes to be in a 24-hour format for some reason.

It would be best for you to provide real-world examples of your CSV file BEFORE and AFTER the changes you want. The before and after formats will dictate the best approach to be used.

@DJLunacy

So assuming I have the file as timestoconvert.csv

Would it be easier to find some Regex to do this?

The formatting of the times appears as time ranges in each line

ex. 7:00 am - 9:00 pm

So, assuming that is an accurate representation of your times, I have developed the following Macro as an example to help get you started.

It uses this RegEx:
(?i)\b(\d{1,2}):(\d{2})\h*(a|p)\.?m\.?
See: regex101: build, test, and debug regex

This extracts the parts of time in the 12-hr format, allowing for these variations in format:

• All spaces are optional
• AM/PM may be in any case, and can optionally include periods

It uses my made-up data, assuming you have other data in the CSV file than just the time:

event1,2020-03-26,7:00 am - 9:00 pm,location1
event2,2020-03-28,11:05 A.M. - 12:34 pm,location2

Below is just an example written in response to your request. You will need to use as an example and/or change to meet your workflow automation needs.

Please let us know if it meets your needs.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Example Output

MACRO:   Convert 12-HR Time to 24-HR Time [Example]

-~~~ VER: 1.0    2020-03-26 ~~~
Requires: KM 8.2.4+   macOS 10.11 (El Capitan)+
(Macro was written & tested using KM 9.0+ on macOS 10.14.5 (Mojave))

DOWNLOAD Macro File:

Convert 12-HR Time to 24-HR Time [Example].kmmacros
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.

---

ReleaseNotes

Author.@JMichaelTX

PURPOSE:

• Convert 12-HR Time to 24-HR Time [Example]

NOTICE: This macro/script is just an Example

• It is provided only for educational purposes, and may not be suitable for any specific purpose.
• It has had very limited testing.
• You need to test further before using in a production environment.
• It does not have extensive error checking/handling.
• It may not be complete. It is provided as an example to show you one approach to solving a problem.

REQUIRES:

1. KM 8.0.2+

• But it can be written in KM 7.3.1+
• It is KM8 specific just because some of the Actions have changed to make things simpler, but equivalent Actions are available in KM 7.3.1.
  .

2. macOS 10.11.6 (El Capitan)

• KM 8 Requires Yosemite or later, so this macro will probably run on Yosemite, but I make no guarantees.

MACRO SETUP

• Carefully review the Release Notes and the Macro Actions
  • Make sure you understand what the Macro will do.
  • You are responsible for running the Macro, not me.
    .
• Assign a Trigger to this maro.
• Move this macro to a Macro Group that is only Active when you need this Macro.
• ENABLE this Macro.
  .
• REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:
  • ALL Actions that are shown in the magenta color

USE AT YOUR OWN RISK

• While I have given this limited testing, and to the best of my knowledge it will do no harm, I cannot guarantee it.
• If you have any doubts or questions:
  • Ask first
  • Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.

[DJLunacy]

Thank You!

I was trying to frame out a solution, yours looks much more robust at first glance compared to where I am at currently.

Currently the file I'm cranking out has the breakdown of 7 days of data. I was testing the process on one isolated day.

I noticed that there's a few entries that have multiple openings and closings, I tried add in some logic checks, but I'm sure it would be much easier if I could figure out how to properly account for those variances.

There's some stylizing html that for some reason is included in the file when there is a multiple opening or closing. It starts with a closing p tag (

) and ends with a "> before the next iteration on the same line so I temporarily just did a search and replace on the string and converted it to *** to try and eventually get them to look like

08:00 - 15:00 16:00 - 19:00

For example

This is a copy of the data I'm using as a source for Monday (after I've manually done a search and replace on the data that isn't needed)

If there isn't an entry in the file and it's a blank line I've replaced the blank line with BLANK

Closed
Open 24 hours

Are the only other variances.

My good friend is trying to get a dynamic list going of local businesses that are opened still or are having changing hours and such due to the virus outbreak, so I'm trying to make a directory for them to use, and am trying to clean up this data dump and get it going .I'm super close! Thanks again for everybody's help, I appreciate it.

Mon.pl (11.6 KB)

[JMichaelTX]

@DJLunacy

My good friend is trying to get a dynamic list going of local businesses that are opened still or are having changing hours and such due to the virus outbreak, so I'm trying to make a directory for them to use, and am trying to clean up this data dump and get it going .I'm super close! Thanks again for everybody's help, I appreciate it.

Mon.pl (11.6 KB)

I don't understand what exactly you are asking for.

When I process your file "Mon.pl" with my Macro it appears to work fine for me.
Have you tried it?

Here is a segment from your file a processed by my macro:

06:00 - 21:00
BLANK
11:00 - 21:00
10:00 - 22:00
11:30 - 22:00
11:00 - 15:00***16:00 - 22:00
11:00 - 02:00
05:00 - 17:00
BLANK
09:00 - 12:00
BLANK
15:00 - 22:00

BTW, it would be best to name text files with a ".txt" extension, unless there is some good reason to do otherwise. I almost did not open your file because I had no idea what a ".pl" file is.

One more note, your file is 726 lines long, so it takes a few seconds to process. You might not notice anything happening until it finished.

[DJLunacy]

@JMichaelTX

BTW, it would be best to name text files with a ".txt" extension, unless there is some good reason to do otherwise. I almost did not open your file because I had no idea what a ".pl" file is.

One more note, your file is 726 lines long, so it takes a few seconds to process. You might not notice anything happening until it finished.

I agree I tried uploading it and it gave me an error about being an unsupported extension, so I just saved it as a pl file because it said it was a supported extension.

PL = Perl script it was a popular programming language before PHP, I used to dabble in it during my days working with UBB (Ultimate Bulletin Board).

Apparently, I really needed the sleep for some reason I assumed it would only snag one-time code per line and the *** would bork it.

---- What I was asking is if in the source file before I sanitize it there was some stray HTML that made its way into the data. I was curious if there's an easy regex command, to take everything between two tags, for example, the ending of a

tag and "> and replace it with say a space?

I'm going to add finding a video tutorial that breaks this stuff down easy peasy to my to-do list.

Thanks, again!

[JMichaelTX]

@DJLunacy

Apparently, I really needed the sleep for some reason I assumed it would only snag one-time code per line and the *** would bork it.

In general, my macro does NOT care what other text is on the line -- it simply looks for 12-hr time matches and replaces them with the 24-hr version.

So, did my macro work, or not?
Let's resolve that issue before asking other questions.