Date-Time Format Problem

[#1]

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.

[#2]

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):Make 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.

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

Hope that helps!

[#3]

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.

[#4 · Accepted solution]

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:

[#5]

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