Subscribe Bookmark RSS Feed

Importing multiple txt files, the time column "hh:mm a.m." is imported as text..


Community Trekker


Jul 29, 2014

Hi!  I'm on the verge of importing multiple (120+ for now) .txt file into JMP, but my one time field is in the format "hh:mm a.m." or "hh:mm p.m."  The a.m. and p.m. are not AM or PM, so JMP recognizes this as text.  What is a clean way to convert this to nice numeric time HH:MM AM or HH:MM PM ?

I don't know how to replace text while I'm importing it.

Any ideas?




Mar 21, 2013

Here's a scripting approach that looks for all the .txt files in a directory, hunts for the .a.m. and p.m. and replaces it, and writes a new file in the same directory.  It also imports the table and saves it.  There is a little bit about Regex here.

You might want to keep the modified files in a separate directory, and you might want to make the originals read-only before you begin.  Could save a lot or re-work later.

This code was tested on a very simple file and may need a bit of touch up for your data.

This assumes there are no other instances of the strings .a.m. or .p.m. in the rest of the data that shouldn't be converted.  If there are. you might want to post process the imported data tables.  That's a bit harder because I don't know about your column names, etc.

path = "$desktop/files/";

list = Files In Directory( path );

For( i = 1, i <= N Items( list ), i++,

    If( Right( list, 4 ) == ".txt" & Left( list, 8 ) != "MODIFIED",

        text = Load Text File( path || list );

        text = Regex( text, "a\.m\.", "AM", IGNORECASE, GLOBALREPLACE );

        text = Regex( text, "p\.m\.", "PM", IGNORECASE, GLOBALREPLACE );

        filename = Save Text File( path || "MODIFIED" || list, text );

        dt = Open(


            Import Settings( Labels( 0 ), Column Names Start( 0 ), Data Starts( 1 ), )


        Close( dt, save( path || Regex( list, "(.*?)\.txt$", "\" ) ) );





Community Trekker


Jul 29, 2014

I’m going to try it right now, but before I do, I want to say “thank you, angel!”

I did go through the scripting guide yesterday to try to get answers and read a little about REGEX. But when it comes to writing scripts, I can follow along with code, but I can’t successfully GENERATE code. It all makes sense when it’s in front of me…


Laura Ellman