Convert separate DATE and TIME columns in formats YYYYMMDD and HHMMSS into a single column

Picard — Thu, 15 Mar 2018 20:58:45 GMT

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?

topic Convert separate DATE and TIME columns in formats YYYYMMDD and HHMMSS into a single column in Discussions

Convert separate DATE and TIME columns in formats YYYYMMDD and HHMMSS into a single column