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
Artemio
Level II

Date-Time Format Problem

Date Time Format Problem

 

Greetings,

 

I am having an issue with a date-time format import from a csv file.  An example of the original data content is

 

17/09/08 02:55:16 AM

17/09/14 00:44:41 AM

17/09/30 17:57:06 PM

17/10/07 00:26:32 AM

 

The format is yy/mm/dd hh:mm:ss AM.  The problem is that JMP does not convert the data into proper format.  A straight import results in

17/09/2008 02:55:16 AM; which is the wrong date.

 

I tried to format the column with

Column(dt1, 2) <<Set Name("Date and Time")<<Data type("Numeric")<<Set Modeling Type( Continuous <<Informat( "y/m/d h:m:s" )<<Format("d/m/y h:m:s");

The result is the same à 2008/09/17 02:55:16 AM

 

A different approach, insert a new column and use a formula

dt1 <<New Column("Date", Numeric, Continuous, Format Date("y/d/m"), Formula( Left("20"||Munger(Loc,6,"20",""), 10)));

 

The result is a string 2017/09/08; but the output needs to be numeric.  Visually, great a step closer.

 

Since things are bit closer, the next three pieces of code were implemented.

dt1 <<New Column("Date1", Numeric, Continuous, Format Date("y/d/m"), Formula( Informat(Left("20"||Munger(Loc,6,"20",""), 10))));

 

dt1 <<New Column("Date2", Numeric, Continuous, Format Date("yyyy/dd/mm"), Input Format("yyyy/dd/mm"), Formula( Parse Date(Left("20"||Munger(Loc,6,"20",""), 10))));

 

dt1 <<New Column("Date3", Numeric, Continuous, Format Date("yyyy/dd/mm"), Input Format("yyyy/dd/mm"), Formula( As Date(Left("20"||Munger(Loc,6,"20",""), 10))));

 

ResulTs are

Date1 column --> 3587673600  

Date2 column --> 3587673600 

Date3 column --> .

 

The Date 1 and Date 2 results are numeric but with the wrong format.  How can I get the right format i.e. numeric, continuous, and date format?  Thank you for help.

1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: Date-Time Format Problem

I'm guessing you did something wrong because I was able to correctly import all the values you put as examples.  I pasted them into excel, saved as a CSV, and then imported using the steps I outlined and got this:

DatesImported.PNG

DatesColumnInfo.PNG

-- Cameron Willden

View solution in original post

4 REPLIES 4
cwillden
Super User (Alumni)

Re: Date-Time Format Problem

Hi @Artemio,

This is actually a pretty simple fix that requires no scripting (though you can script this).  In the absence of direct input from you about the date format, JMP is going to guess.  It very often gets this wrong with 2-digit years.  When you go to open the file, you should see some options like this (at least on Windows):OpenCSV.PNGMake sure to select "Data with Preview" when you open it so you will have a chance to specify the informat of the date column.  You will see a window that lets you modify the import settings for the csv file.  If you click "Next", you will get to set the informat setting on the next page.

 If you click the red-arrow next to the column with the date format you need to set, you will get the list of informats available.  Select Time > "y/m/d h:m:s".  Now you can import.InformatSelect.PNG

 If you want to know how to script this import setting, check out the Source script on the resulting data table.

Hope that helps!

 

-- Cameron Willden
Artemio
Level II

Re: Date-Time Format Problem

Hello Cameron,

 

Thank you for the quick reply.  Your suggestion was my initial inclination but it does not work.  JMP processes the file and automatically changes the date to something that is not correct.  For example, 17/09/08 02:23:52 is imported as 17/09/2008 02:23:52 AM; which is wrong.  Since the output is incorrect, I had to explore other options (JSL) -modify and correct the output.  Thank you for your input.  It is greatly appreciated.

cwillden
Super User (Alumni)

Re: Date-Time Format Problem

I'm guessing you did something wrong because I was able to correctly import all the values you put as examples.  I pasted them into excel, saved as a CSV, and then imported using the steps I outlined and got this:

DatesImported.PNG

DatesColumnInfo.PNG

-- Cameron Willden
Artemio
Level II

Re: Date-Time Format Problem

Hello Cameron,

 

I got a hold of other csv files that have the same format.  I followed your instructions with a different csv file and this time it worked as you have suggested.  Thank you