Setting CSV Import Default Date Time Format

[#1]

I've been using a modified version of the script here Combine-multiple-txt-or-csv-files to import multiple (1000's) of CSV files. The problem is that it imports any date in MM/DD/YY which, if you don't live in the USA, means that the dates flip-flop between being correct and incorrect.

The only solution I've found is that you can specifiy the import settings when creating the temp file to hold the data:

dt=open(fileopen,private,
			
	New Column( "CycleStartDate&Time",
		Numeric,
		"Continuous",
		Format( "d/m/y h:m:s", 22, 0 ),
		Input Format( "d/m/y h:m:s", 0 )
	),
	New Column( "CycleEndData&Time",
		Numeric,
		"Continuous",
		Format( "d/m/y h:m:s", 22, 0 ),
		Input Format( "d/m/y h:m:s", 0 )
	)
);

This seems like a bit of a backwards way to do it as if the name of the date field changes it will revert back to MM/DD/YY!

I've been scratching my head for about about an hour and a half (or 30:01 if you're American) but can't find a nicer way to do this...

[#2]

In fact that solution does not work: for example the date saved as "10/1/2018 9:50:46" is imported into JMP as "01/10/2018 09:50:46".

[#3]

I still haven't found a good solution to this other than creating a whole new temp file with each column setup before importing the data into it.

Does anyone know how to fix this file import bug?

[#4 · Accepted solution]

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

[#5]

Thank you for the comprehensive response! I'll have to get my head around the work-around you've posted to understand how to incorporate it into my script

[#6]

Welcome!

If you have access to JMP 14 and are opening 1000's of CSVs, please take a look at the multiple file import. It is likely to be a lot faster because it uses threading to open multiple files at a time.