Setting CSV Import Default Date Time FormatI'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...
Re: Setting CSV Import Default Date Time FormatWelcome!
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.
Re: Setting CSV Import Default Date Time FormatThank 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 scriptRe: Setting CSV Import Default Date Time Format
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<dt<<newcolumn("month2",formula(month(date2)));
date1 is d/m/y, date2 is m/d/y, source script high lighteddate1 is d/m/y, date2 is m/d/y, source script high lighteddate1 is d/m/y, date2 is m/d/y, source script high lighted
The Source script looks like this
High lighting the two date formatsHigh lighting the two date formatsHigh 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<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<dt<<newcolumn("month2",formula(month(date2)));
date2 is now d/m/ydate2 is now d/m/ydate2 is now d/m/y
Re: Setting CSV Import Default Date Time FormatI 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?
Re: Setting CSV Import Default Date Time FormatIn 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".
