CVS files with date columns open as characters

godfeatha

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!

Craige_Hales

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.