Hello,
I manage an addin for my company where all data is imported into JMP via command line utilities owned by our automation team. My addin translates a users request into a formatted command and issues the command in the background. The data is returned back as a text file on the users system. My addin can then load the data from the text file.
The issue that I have is that the columns vary based on the users request. But, each table has a DATE (Format: YYYYMMDD) and TIME (Format: HHMMSS) column that are consistently there. I have not found a way to open a text file where I specify the formats for some columns but let JMP interperet the data types for the rest.
The current implementation requires that I convert the DATE and TIME columns into character types, format the time into the HHMMSS with leading 0's, and then loop through the table to parse the DATE column into a date and convert the HH, MM, and SS parts into seconds so that I can add them to the DATE portion.
dt = Current Data Table();
:DATE << Data Type(character);
:TIME << Data Type(character);
:TIME << Set Each Value(Repeat("0", 6-Length(:TIME))||:TIME);
dt << New Column("DATETIME");
:DATETIME << Format("m/d/y h:m:s");
For Each Row(
:DATETIME[Row()] = Parse Date(:DATE[Row()], "YYYYMMDD") +
In Hours(Num(Substr(:TIME[Row()], 1, 2))) +
In Minutes(Num(Substr(:TIME[Row()], 3, 2))) +
Num(Substr(:TIME[Row()], 5))
);
Is there a cleaner way to join the DATE and TIME columns into a single DATETIME column?
