Several answers, none perfect:
- In JMP 14 you can use multiple file import, which will leave the data in character format. You can then change the column format from character to numeric with the appropriate data format, manually, or with scripting.
- In JMP 13 specifying the column format should work.
- JMP's text file import looks at all the dates in the a column to find a day > 12 to infer the proper format; currently the fallback is mm-dd-yyyy. I will look at creating a preference for that.
- Here's a work-around that might help.
First, create a table with an ambiguous column:
filename = savetextfile(
"$temp/date.csv","date1,date2
31/10/2018 23:59:59,2/5/2018 00:00:00
1/9/2018 00:00:00,3/7/2018 00:00:00"
);
// date1 is clearly mm/dd/yy
// date2 is not clear, there are no days > 12
dt = open(filename);
// examine what happened
dt<<newcolumn("month1",formula(month(date1)));
dt<<newcolumn("month2",formula(month(date2)));
date1 is d/m/y, date2 is m/d/y, source script high lighted
The Source script looks like this
High lighting the two date formats
JSL can edit the source script, substituting the desired format. Then run the modified script and add some columns to show off the month:
script = dt<<GetProperty("source");
close(dt,"nosave"); // no longer needed
// change m/d/y to d/m/y
substituteInto(script,"m/d/y h:m:s","d/m/y h:m:s");
// re-open the table with the modified script
dt = eval(script);
// examine what happened
dt<<newcolumn("month1",formula(month(date1)));
dt<<newcolumn("month2",formula(month(date2)));
date2 is now d/m/y
Craige