CVS files with date columns open as characters

[#1]

Hi all,

Many times I open a csv file that has a column that contains dates. Every once in a while when I a csv file, JMP opens it as a charcter. When I try to converrt it to date, I the vast majority of the rows for that comlumn are erased (null). Any idea why this is happening?

Thanks in advance!

[#2]

Select the data column, select Cols > Column Info. Change the Data Type to Numeric. Then change the format to one that matches the character string date pattern. Click OK.

[#3]

Thanks, but when I do that, I lose the vast majority of my data for that column. Any idea why?

[#4]

Are all the values consistent with the selected format? Do any values include more than just the date?

[#5]

All rows contain only the date format.

[#6]

Can you share an example of a date that works and one that fails? If JMP guesses mmddyyyy vs ddmmyyyy and gets it wrong, a significant number of dates will become missing values (for months 13 to 31). You can tell JMP what format to use (easiest in the preview wizard) and then use that script for future imports. When you get the character column, JMP has found something that suggests the column is not all one format that JMP recognizes. You should specify the proper format at the same time you convert from character to numeric. 

[#7]

Thanks Craige. Here's an example of a csv that works.

[#8]

And here's the bad example.

Thanks, Eliav.

[#9 · Accepted solution]

JMP needs to be told what date format to use. If the file begins with a date (13/12/2019) that is clearly d/m/y rather than m/d/y then JMP guesses d/m/y correctly. But when the file begins with an ambiguous date (11/10/2019), JMP guesses m/d/y and then has to deal with later values that can't be converted. You should always specify the format if d/m/y is used. You can do that in a script:

Open(
	"E:\BadDates.csv",
	columns(
		New Column( "lot", Character, "Nominal" ),
		New Column( "WaferID3", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "MeasurementDate", Numeric, "Continuous", Format( "d/m/y h:m", 19 ), Input Format( "d/m/y h:m" ) ),
		New Column( "Other_date", Numeric, "Continuous", Format( "d/m/y h:m", 19 ), Input Format( "d/m/y h:m" ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

I used the import wizard to specify the date format, then copied the source script from the imported table.

You can also convert character columns in the column dialog by (do both at once!) changing character to numeric AND change to the right date format.

But you really need to specify the date format when the column of d/m/y is imported to avoid the case where all the dates are ambiguous and are assumed to be m/d/y; JMP won't be able to tell you anything is wrong in this case, but the internal date values will have the month and day switched...making the dates sort incorrectly, etc.

[#10]

Thanks!