Setting CSV Import Default Date Time Format

David_

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...

Craige_Hales

 

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