topic Re: csv file: Day.Month.Year in Discussions

csv file: Day.Month.Year

hogi — Thu, 11 Apr 2024 11:47:13 GMT

In a few countries date is saved as Day.Month.Year.

Unfortunately, Jmp assumes date to be Month.Day.Year - even when regional settings are enabled in the Preferences.
If there are some dates with first value >12, Jmp will automatically detect it and change to the non-standard version
This behavior looks quite dangerous - just

Is there another setting in the preferences where I can specify the date format such that Days and Months don't get intermixed.

Re: csv file: Day.Month.Year

Craige_Hales — Thu, 11 Apr 2024 16:39:34 GMT

I think you have to do two things: the OS setting and the JMP setting.

Re: csv file: Day.Month.Year

hogi — Thu, 11 Apr 2024 19:00:52 GMT

Hm, I seem to be doing something wrong.

My system is configured like this:

(TT = DD)

Jmp:

and when I read a file with DD.MM.YYYY, JMP reads it as MM.DD.YYYY.
Same when entering values in a cell:

(view in My Videos)

Re: csv file: Day.Month.Year

Craige_Hales — Fri, 12 Apr 2024 11:11:59 GMT

The column's input and output formats are both MDY; the video shows the behavior I expect. You might want to change both. You probably don't want them opposite of each other, but setting the output format to 31JAN2024 (ddmonyyyy ?) might help explain what you are seeing. I'm not sure how those 3-letter month abbreviations will work for you either, but it does clear up the ambiguity.

The OS's regional settings, and JMP's Use Regional checkbox, apply to text import (CSV). I don't know if JMP uses the regional settings in reports.

The checkbox was added because there are a lot of CSV files that use DMY and a lot of international users that have the OS set to MDY. It is easier to clear Use Regional to ignore the OS setting than it is to change the OS setting. Often you'll want CSV files processed as DMY, but not always. I think the comma/period/etc for separator/decimal indicator is also part of this discussion. The single checkbox is overloaded; if you need finer control, say to ignore the OS setting for decimal but use the OS setting for date, you'd need to change the OS setting and check Use Regional.

https://en.wikipedia.org/wiki/List_of_date_formats_by_country

https://en.wikipedia.org/wiki/Decimal_separator

Re: csv file: Day.Month.Year

hogi — Fri, 12 Apr 2024 13:46:01 GMT

What do I have to do to directly import D.M.Y without further adjustments.

Re: csv file: Day.Month.Year

Craige_Hales — Fri, 12 Apr 2024 16:02:59 GMT

use the CSV import wizard and specify the correct format for the column. (Not MFI, use the file->open->text files and ask for the wizard.) Once you have the script, you can edit it as needed.

Re: csv file: Day.Month.Year

hogi — Sat, 13 Apr 2024 06:57:14 GMT

I would love to have a setting in the preferences which guarantees that none of our new JMP users has to fear that JMP accidentally uses a wrong format.

dt = New Table( "dates",
	Add Rows( 30 ),
	New Column( "YMD",
		Format( "y/m/d" ),
		Formula( In Days( Row() ) )
	),
	New Column( "DMY",
		Format( "d/m/y" ),
		Formula( In Days( Row() ) )
	),
	New Column( "MDY",
		Format( "m/d/y" ),
		Formula( In Days( Row() ) )
	),
	New Column( "DMY2",
		Format( "d/m/y" ),
		Formula( if(row() < 12,In Days( Row() ) ))
	)
);

dt << save("$temp/dates.csv");

dt2 =open("$temp/dates.csv")

Re: csv file: Day.Month.Year

Craige_Hales — Fri, 12 Apr 2024 23:16:05 GMT

agree. wish:

Re: csv file: Day.Month.Year

Craige_Hales — Fri, 12 Apr 2024 23:21:55 GMT

Multiple File Import avoids the issue by importing dates as character so you can change them to the proper format/numeric afterward.