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

laura_ellman1

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?

Craige_Hales

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(
            filename,
            Import Settings( Labels( 0 ), Column Names Start( 0 ), Data Starts( 1 ), )
        );
        Close( dt, save( path || Regex( list, "(.*?)\.txt$", "\1.jmp" ) ) );
    )
);