cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
godfeatha
Level I

CVS files with date columns open as characters

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: CVS files with date columns open as characters

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.

 

Craige

View solution in original post

11 REPLIES 11

Re: CVS files with date columns open as characters

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.

godfeatha
Level I

Re: CVS files with date columns open as characters

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

Re: CVS files with date columns open as characters

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

godfeatha
Level I

Re: CVS files with date columns open as characters

All rows contain only the date format.

 

Craige_Hales
Super User

Re: CVS files with date columns open as characters

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. 

Craige
godfeatha
Level I

Re: CVS files with date columns open as characters

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

godfeatha
Level I

Re: CVS files with date columns open as characters

And here's the bad example.

Thanks, Eliav.

Craige_Hales
Super User

Re: CVS files with date columns open as characters

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.

 

Craige
godfeatha
Level I

Re: CVS files with date columns open as characters

Thanks!