JMP16 Using Custom Date Formats In Scripts

[#1]

Hello, 

I am working on a data table in which the date is formatted as such: 15/09/08/19:12 . 

I have managed to convert this using the custom format feature in the data table editor with the following: <YY>/<MM>/<DD>/<hh24><::><mm> but via script I cannot figure how to get this to work. 

Below is an exert of the script, any help would be greatly appreciated. 

Best Regards, 

Luke

path = Get Default Directory(); 
dt = Pick File("Select .csv file with  data", path, {"Excel|csv;xlsx;xls", "JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, ""); 


Open(dt,
	columns(
		New Column( "; DateTime", Numeric, "Continuous", Format( "y/m/d h:m"),Input Format( "<YY>/<MM>/<DD>/<hh24><::><mm>", 3 )),
		New Column( "Machine ID", Character, "Nominal" ),
		New Column( "Z/Xrmv_max", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Z/Xrmv_min", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Z/Xrmv_max-min", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "P/Xrmv_max", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "P/Xrmv_min", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "P/Xrmv_max-min", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 1 ) ),
		Strip Quotes( 0 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

JMP 

[#2]

Syntax when using Format pattern is a bit different. Try something like:

Format("y/m/d h:m"),
Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>")

Example table

New Table("Untitled",
	Add Rows(1),
	New Column("Column 1",
		Numeric,
		"Nominal",
		Format("y/m/d h:m"),
		Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>"),
		Set Selected,
		Set Values([3061825920]),
		Set Display Width(89)
	)
);

[#3]

Thank you @jthi, 

I am still a having issues where the column still is giving null results:

The column is changing to the desired format as if performing manually. Could it be because the original column name is not changing due to the name being "; The schema definition " ?

Thanks again. 

[#4]

If you try importing it first as character, what does it look like? Only empty cells?

New Column( "; DateTime", Character, "Nominal),

[#5]

If taken as Character we get the following:

Also, in regards to my previous reply, I edited the original csv and removing the ";" does not change the issue. 

[#6 · Accepted solution]

I created quick test.csv with only couple of rows and used the option to directly modify it in the import platform:

Script:

Open(
	"$DOCUMENTS/JMP/test.csv",
	columns(
		New Column("time",
			Numeric,
			"Continuous",
			Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>", 14),
			Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>")
		)
	),
	Import Settings(
		End Of Line(CRLF, CR, LF),
		End Of Field(Comma, CSV(0)),
		Strip Quotes(1),
		Use Apostrophe as Quotation Mark(0),
		Use Regional Settings(0),
		Scan Whole File(1),
		Treat empty columns as numeric(0),
		CompressNumericColumns(0),
		CompressCharacterColumns(0),
		CompressAllowListCheck(0),
		Labels(1),
		Column Names Start(1),
		Data Starts(2),
		Lines To Read("All"),
		Year Rule("20xx")
	)
)

So maybe the "result" format must also be done with Format Pattern?

[#7]

Hi @jthi , thank you so much! That has worked and has fixed the issue!