Force import format of Excel date column

[#1]

Hi JMP Community,

  I'm looking for help with forcing a date format import using the Excel Import Wizard via JSL. I'm trying to import data that is in only two columns in an Excel file, one date, one data, it looks like the following:

  The problem I have is at the transition from the dates 12.02.2019 to 13.02.2019 (marked by red at the left side). For some reason, JMP is reading the date values from the start to the last 12.02.2019 value as dd.mm.yyyy format, but then switches at 13.02.2019 to reading dates as mm.dd.yyyy format. When I go into the Excel file and review the formatting for the cells, they're identical. I can't figure out why JMP is suddenly switching. When it makes this switch, it leaves the date cells empty in the JMP table.

  If I import the data in two different blocks, those from the start to the last dd.mm.yyyy format, and a second one for the others, it reads each date correctly, however the Column Info for the two different dates are formatted differently and are incompatible with each other. If I try concatenating the two sub-sets, it just switches the day/month of whichever table I concatenate to.

  I've also tried changing several of the toggle options in JMP preferences to try and force system or JMP settings, but all were unsuccessful.

  If I can import the date as a character, I can modify things accordingly and then switch it back to a continuous data type, and it should be all OK. The only problem is I am not sure how to force this, or if this is the best way.

  As with most automation attempts, I STRONGLY prefer to not go in and edit every Excel sheet or file. I want to have JMP do this via JSL script.

  The JSL code I use to import is:

Open(
	"file_location\file.xlsx",
	Worksheets( "Sheet 1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 15 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 16 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
)

  Is it related to the "Limit Column Detect(0)" option? I can't find any documentation on this and what it does.

  This issue is similar to one posted by @ghartel back in May of 2017 (https://community.jmp.com/t5/Discussions/Excel-Import-Date-Format/td-p/16619), which didn't get a direct solution to their specific issue, at least as far as I can tell.

  Any help is much appreciated!

Thanks,

DS

[#2]

Hmmmm, @SDF1, you might have found a bug.

The approach I take is to read in the data, let it be a string then convert it. However, if I try to use only the last Data Type() message, I see the same behavior that you described. If I keep the same informat and format statement, then the problem is not seen.  Once converted, then set to your chosen format 

Names default to here(1);
dt = Open(
	"C:\temp\ExcelDateBlog.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
wait(0); //change to 2 seconds to see that the data is read in as text
dt:Date << Data Type (Numeric,
		"Continuous",
		Format( "ddmmyyyy", 12 ),
		Input Format( "ddmmyyyy" ));

dt:Date << Data Type (Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "ddmmyyyy" ));

Hoppe that helps.

[#3]

The Excel Preview looks at the first 100 rows to determine the data type for the preview, for performance reasons.  During the actual import operation, JMP looks at all the rows.  If it sees data that leads it to a different conclusion after row 100, it can produce a different result.  If you want the Preview to look at all the rows to produce what you will see on import, you can select the "Show all rows" option in the Preview Pane Refresh of the UI.  If you want the import to look at only the first 100 rows when reading the data, to produce the behavior of the Preview, you can go to "Advanced Options" in the second pane of the import dialog and select "Limit column type detection".

JMP 15 will introduce the ability to force the numeric formatting types of individual columns to the whatever the user chooses.

Brian Corcoran

JMP Development

[#4]

Hi @briancorcoran,

  Thanks for the input. I tried your suggestion, but unfortunately, it did not work. Even in the preview pane when "limiting column detect" and showing all rows, JMP has an issue with reading in the format. It reads in the first 205 rows just fine, it's at row 206 where it switches format for some reason.

  What doesn't make sense is that the formatting within Excel doesn't change from one row to the next. If it did, I can understand why JMP might read them in differently. All cells are formatted as "Date" dd.mm.yyyy in Excel. The same thing happens on the next tab that I'm importing, but at rows 52 to 53. I could understand if there was an issue in the original Excel file generation where the same rows across all tables had some glitch that saved the dates differently, but it's not even at the same location from tab to tab when JMP reads in the data.

  The only formatting thing from the Excel side that I see could be that the "type" option in the "date" category starts with an *, see attached image below. But, this should update according to the OS settings. All dates in the column are formatted this way, so if JMP has an issue with one, it should have an issue with all.

  I will try @gzmorgan0's suggestion for importing and see if that works.

  That will be nice to have JMP force certain formatting types during import, especially when it comes to building a JSL code for automating the process.

[#5]

Hi Brian,

I'm trying to import excel data and struggling to get it in the format I want. I noticed you said the JMP 15 has additional import formatting capabilities. Since I'm using JMP15, I was hoping you could help me on this.

The time data in excel (1st column) has milliseconds attached so I need to force the import to recognize that. When imported, JMP just gives the  format("d/m/y h:m:s",27,0). I need to have it as  format("d/m/y h:m:s",27,3). If this is my code, where does the formatting for that decimal to 3 go?

Open(
    "file_location\file.xlsx",
    Worksheets( "Sheet 1" ),
    Use for all sheets( 1 ),
    Concatenate Worksheets( 0 ),
    Create Concatenation Column( 0 ),
    Worksheet Settings(
        1,
        Has Column Headers( 1 ),
        Number of Rows in Headers( 1 ),
        Headers Start on Row( 1 ),
        Data Starts on Row( 2 ),
        Data Starts on Column( 1 ),
        Data Ends on Row( 0 ),
        Data Ends on Column( 0 ),
        Replicated Spanned Rows( 1 ), 

        Replicated Spanned Headers( 0 ),
        Suppress Hidden Rows( 1 ),
        Suppress Hidden Columns( 1 ),
        Suppress Empty Columns( 1 ),
        Treat as Hierarchy( 0 ),
        Multiple Series Stack( 0 ),
        Import Cell Colors( 0 ),
        Limit Column Detect( 0 ),
        Column Separator String( "-" )
    )
);

[#6]

Assuming that the data that are downloaded contain the milliseconds, then all that has to be done is to change the format once the data table is imported.  And another assumption for this example is that the column that has the date and time value is called DateTime, the following JSL can be applied to change the data display.

:DateTime << set format( "d/m/y h:m:s", 27, 3 );

[#7]

Thanks for such a quick response!

The issue is actually during the import (I think) so running that as a post process hasn't worked for me.

So the excel data in the first column looks is formatted like this:

26/11/2019 09:41:04.190 PM

When I import it into JMP using open(filepath, worksheets()….) the data is formatted like this:

26/11/2019 9:41:04 PM

If I change the format once it is imported the result is this:

26/11/2019 9:41:04.000 PM

So It looks like the last three digits are dropped during the import. Is there a way to force that format as it is imported into the JMP table?

[#8]

The way that I have seen issues like this overcome in the past, is to read the column in as a character string, and then to post process into a date value.

[#9]

Ok great! Thanks!

What is the script format I should use to read that as a string when I import?

[#10]

You can try using the "Limit Column Detect" option, along with your custom formatting.  You will likely need JMP 15 for this to work.  The following worked for me:

Open(
	"C:\Users\sasbxc\Desktop\test.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 1 ),
		Column Separator String( "-" ),
		Column Numeric Format(
			Index( 0 ),
			Column Name( Col1 ),
			Format( "d/m/y h:m:s", 26, 3 )
		)
	)
)

Brian