Using Excel Wizard to import time with milliseconds

Question

I am trying to import time data with milliseconds.  It is in hh:mm:ss.000 format in Excel.

If I simply copy and then "paste with column names" into JMP, it imports correctly.  JMP does not show the milliseconds but it does interpret it correctly, and when graphing a response variable against the Timestamp column, the graphing works correctly, generating points appropriately spaced to coincide with the number of milliseconds.

As an example, if the Excel cell contains "10/11/2021 12:46:57.894", it looks like "10/11/2021 12:46:58" in JMP.  This is true for all cells imported from Excel that are in the 12:46:58 time frame.  While the JMP cells all look the same, they graph correctly, so JMP does know the millisecond value.

But if I use the Excel Wizard to import, the JMP cell looks like "10/22/2021 12:46:58.000"  When you attempt to graph a variable against the Timestamp, all of the points for that second graph together in a jumbled pile of dots, so JMP has lost the millisecond value using the Excel Wizard.

Is there a way to use the Excel Wizard to import this data while still retaining (and ideally displaying) the millisecond values?

Test.xlsx

brady_brady · Accepted Answer

Hello,

I have been working on this for a bit, and believe there may be an issue with the Excel importer in this case. At any rate, I am unable to get the Excel importer to do what you wish. I will submit a Tech Support track to investigate, but in the meantime, I've found a workaround which I hope will work for you.

JMP's text import wizard happily imports the data in the way you've specified. If you save off the Excel file as a text-delimited file (personally I recommend the.csv format), you can easily import the data.

After exporting the .xlsx file to .csv, be sure when attempting to open it the .csv file with the "OPEN..." command on the main menu that the "use default program" box is cleared. This allows you to select the "Data (Using Preview) radio button:

Select the desired file (which is now a .csv file), click "Open", and when the wizard appears, ensure the following selections (which are defaults) are made:

Click NEXT, then IMPORT, and a table is created:

Right-clicking on the SOURCE script in the upper left of the table and selecting "Edit" will show you a script you can run to bypass the wizard in the future, making it easy for you to recreate this table with the most recent version of the .csv file, whenever you wish.

Cheers,

Brady

Audrey_Shull · Accepted Answer

Great workaround, @brady_brady! I can also report that some work has been done in this area in the JMP 17 development cycle, and we expect JMP 17 will be able to honor a decimal for seconds in order to support MS. JMP 17 will be available in the fall of 2022.