topic Re: Importing Excel Files Containing Mixed Date Formats into JMP results in missing values in Discussions

Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

Florent_M — Mon, 14 Oct 2024 09:24:32 GMT

Hello!

I am currently facing an issue while importing an Excel file that contains a date column into JMP. This column includes two different formats: "DD/MM/YYYY" and "DD/MM/YYYY hh:mm". The "DD/MM/YYYY" format should be interpreted as midnight, i.e., "DD/MM/YYYY 00:00".

However, when I import the file into JMP, whether via script or the GUI, whatever import option used, JMP seems to import missing values for one of these two formats, depending which format is on the second row. Please refer to the attached Excel sample for a better understanding.

My objective is to automate the processing of multiple files using a script that can handle this inconsistency.

I would greatly appreciate any assistance or guidance you can provide. Thank you for your support.

Kind regards,

Florent

Re: Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

jthi — Mon, 14 Oct 2024 10:00:10 GMT

You could for example import your data as characters and then fix it by using JSL or JMPs tools after. You should be able to do this by telling JMP that there are no headers and data starts on row 1

Re: Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

txnelson — Mon, 14 Oct 2024 10:45:55 GMT

I can not replicate your findings. I am running on a Windows 11 system and imported your sample file under JMP 18, 16 and 14. None of these imports read in missing values. I did have to change the display format to "m/d/y h:m:s" for the times to be displayed.

What version of JMP are you using, and on what operating system.

Re: Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

Florent_M — Mon, 14 Oct 2024 12:07:32 GMT

I use windows 11 and JMP18.0.1. I send you the jmp table that I have when I open the file with default setting.

Thanks,
Florent

Re: Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

Florent_M — Mon, 14 Oct 2024 12:08:23 GMT

It is not straight forward, but it works.

Many thanks,
Florent