topic Re: Force import format of Excel date column in Discussions

Force import format of Excel date column

SDF1 — Mon, 15 Apr 2019 20:42:27 GMT

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,

Re: Force import format of Excel date column

gzmorgan0 — Tue, 16 Apr 2019 01:34:43 GMT

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.

Re: Force import format of Excel date column

briancorcoran — Tue, 16 Apr 2019 11:58:57 GMT

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

Re: Force import format of Excel date column

SDF1 — Tue, 16 Apr 2019 13:26:11 GMT

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.

Re: Force import format of Excel date column

SDF1 — Tue, 16 Apr 2019 19:26:29 GMT

Hi @gzmorgan0,

Thanks for your thoughts and input. Unfortunately, this approach also doesn't work. The problem stems from JMP misreading the date format before it even imports it. If I run the script or try to do it throught he wizard GUI, JMP misclassifies the dates as either a d.m.y or m.d.y format when it's the other one.

Even modifying your code for different variations in date format doesn't solve the problem. It simply doesn't import the mixed format and only treats one kind or the other as continuous, the others it'll just ignore. The JSL code won't import it as a nominal data type either -- it comes straight in as continuous.

Thanks!,

Re: Force import format of Excel date column

SDF1 — Tue, 16 Apr 2019 20:23:40 GMT

Update:

I did a little more digging into the Excel file and the one common thing across all tabs that causes this mistake when I try to import the data is when it reads down the date column and goes from the date 12.02.2019 (12th Feb, 2019) to 13.02.2019 (13th Feb, 2019).

Here's my theory: I think JMP is mixing up the format for the dates before the 12th to after. My original date range is from 02 Jan 2019 to 28 Feb 2019: 02.01.2019 to 28.02.2019.

As JMP is reading down the date column, it appears to actually be interpreting the date 02.01.2019 as 01 Feb 2019, so when it gets to 12.02.2019, it's actually reading in the date as 02 Dec 2019. As a consequence, when it gets to 13.02.2019 (13 Feb 2019), it doesn't know how to read in the date since there is no 13th month. The column properties in JMP always show it as "continuous" modelying type, "d.m.y" format and "d.m.y" input format. It does this unless I split the import into two sections.

If I split the import into two different parts, one from 02.01.2019 to 12.02.2019 and then from 13.02.2019 to 28.02.2019, JMP imports the data appropriately and assigns the column format appropriately. I can then concatenate the two into a correct data table. I don't know why it wasn't working in my original post, but it can work this way.

I can also confirm that there is no dependence on the data being correctly imported on what preferences I've set within JMP.

This is not so conducive to automation since the original file might change the location (row) of this 12th/13th date change. I guess I might have to make it work via the split route, though. If this is a bug in JMP, it would be great if this could be fixed.

Re: Force import format of Excel date column

briancorcoran — Tue, 16 Apr 2019 20:32:29 GMT

Hi DS,

JMP should honor formats that are applied specifically to a column in Excel. Have you gone into Excel and use the Format Cells dialog to explicitly set the column type to Date and then a European format like German d.m.y? In that case JMP should bring the data in correctly.

Brian

Re: Force import format of Excel date column

SDF1 — Tue, 16 Apr 2019 20:37:35 GMT

Hi Brian (@briancorcoran),

Yes, in fact the column is set to "date" with a German style d.m.y. format. Please check a previous post in this thread where I include a screen shot of the Excel column property window and the setting. Even my system setting is like that, see below.

Thanks!,

Re: Force import format of Excel date column

briancorcoran — Tue, 16 Apr 2019 20:41:55 GMT

What version of JMP are you using?

Brian

Re: Force import format of Excel date column

gzmorgan0 — Tue, 16 Apr 2019 22:11:25 GMT

@briancorcoran, did you see my rely to @SDF1 ?

I read in the Excel sheet as text/character
Then sent the column message

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

The same behavior described by @SDF1 occurred as well. Ir read the first data then at 13.02.2019 and later the values were empty. This has nothing to do with the Excel sheet: JMP read the Date column as text.
However, if I used the column message below where there are not 2 conversions, the input Format and Format are the same, then the data is converted correctly.

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

Then use the previous column Data Type() message and the chosen format is okay.

As stated in my response, this was very unexpected behavior and seems to be a bug to me. I am using JMP Pro 14.3

Re: Force import format of Excel date column

gzmorgan0 — Tue, 16 Apr 2019 22:19:52 GMT

@SDF1, Sorry it did not work for you.

Not being from Germany, JMP interpretted the dd.mm.yyyy format as text.

You might try one of these suggestions:

make that Date column a text column.
save the data as a text file, where the Import Preview, or a scrcipt allows you to set the format and force it to be character.

I do think there is an internal bug. See my last post to Brian.

Re: Force import format of Excel date column

briancorcoran — Wed, 17 Apr 2019 00:23:28 GMT

Thanks Georgia. I’ve sent this on to investigate as a possible bug.

Brian

Re: Force import format of Excel date column

SDF1 — Wed, 17 Apr 2019 12:19:38 GMT

Hi @briancorcoran,

Sorry, I keep forgetting to put my system details at the start of a new post.

W7 Enterprise, 64-bit

JMP Pro 14.1.0 (and JMP 14.0.0)

I am running an English (US) OS and trying to write some automation code for my German colleagues that are also running W7 Enterprise, but set to a German language for the OS. With other projects like this, I haven't had any issues with the dates being misread. I do have to write two different versions of code though, one for my US colleagues and one for my German colleagues because the normal US OS's read in the dates as text. And as @gzmorgan0 has mentioned, when it's read in as text, handling and converting it is no problem. But, if my German colleagues try to run the JSL scripts that were written to run on US computers, it doesn't work well, so I have to change the code to run correctly on a German language OS.

In order to write code for running on the German computers, I change my regional/local settings in my OS to "German (Germany)". Unfortunately, JMP does not seem to import the date settings correctly from the Excel file I have.

Re: Force import format of Excel date column

gzmorgan0 — Wed, 17 Apr 2019 23:30:05 GMT

@SDF1, I have two suggestions:

JSL via Run Program() can run other programs such as VBSript. Your JSL could be written to Open teh file via Excel, format the column as text (if needed); save the file as tab delimited text; close Excel then read in the text file where you have the option to read the column as text and then convert.
Excel Files can be used as a database. I did a little reading and the probelm your German colleagues are seeing might be due an Excel converter for the data type. Below is JSL to use Open Database(). This takes a little prep work. Your German colleagues would need to define a DSN for Excel and use the same name. Now if everyone has 64 bit JMP and 64 bit MW Office that is pretty easy. If they have 32 bit MS Office, I can send the steps to take to set that up. The code IMEX=1 tells Excel if it sees a column that looks like a mixed data type to Excel the convert it to text.

dt2 = Open Database( "DSN=Excel Files;
    DBQ=c:/temp/ExcelDateBlog.xlsx;IMEX=1",
	"SELECT * FROM [Sheet1$]",
	"Result from XLS"
);

I think working with a text file would be easier until JMP 15 when the Excel wizard allows specfiying the column formats. Let me know if you need help/example for option #1.

Re: Force import format of Excel date column

ghartel — Fri, 19 Apr 2019 05:00:43 GMT

Hi JMPers,

Australia also uses the "incorrect" date format :( and so days are interpreted as months and >12 become missing value due to format violation. This can happen on the second line. it is very frustrating because if you're unlucky the first day > 12 can happen down the file where you don't notice and you end up with a missing value without warning as JMP decides it has read an incorrect date format.

There are two things that might make it work. First in JMP Preferences under 'Windows Specific' unckeck the box 'Use JMP language rather than System Locale settings...'. I cant find any place in JMP where you can set a default date format - so not sure what this flag refers to. The second step is to change the windows locale setting as the previous poster showed, BUT JMP doesnt like the dd/mm/YYYY setting - it has to be dd/MM/yy - don't know why, but then it works at least for me. Don't know if this is jsut a Windows problem, ie if Macs don't have this problem. I run JMP on a windows 7 machine and a windows 10 machine - JMP PRO 14.3 64 bit, but I've had this problem since Excel imports were a thing in JMP.

Some suggested reading in the column as text - I can't see any options for forcing JMP to read specific columns in as text - is that possible?

Another workaround is to get your data in CSV format as for some reason JMP allows very minute control over inputs unlike for Excel. The third option is to read in Excel files using the database interface. But that requires appropriate ODBC settings on your PC and it is a bit of a pain too.

Note to JMP - it would be very good if JMP sent some warning or error messages if it fails to import a field due to wrong format. I have clients who add * or < to numeric fields which Excel happily turns into character fields and JMP happily and silenty turns into missing values. JSL is fantastic for automating tasks but this sort of thing makes it very hard to error proof your programs.

Cheers

Gunter

Re: Force import format of Excel date column

gzmorgan0 — Fri, 19 Apr 2019 13:05:20 GMT

@ghartel, Note that @briancorcoran mentioned there might be more options in JMP 15.

Attached is a script that saves an Excel sheet as a tab delimited text file then reads in the text file into JMP.

You might want to try reading the text file with your system defaults (in Australia and Germany), to see if the JMP import text defaults will read the Date as a text. If not, then you have to name all your columns and specify the data type.

Since this script uses VBScript, it likley doe not work for a Mac.

The script is provided as a "just in case this might help."

Re: Force import format of Excel date column

SDF1 — Fri, 19 Apr 2019 13:50:42 GMT

Thanks @gzmorgan0, especially for the example import file. I'll be able to test it out next week and see if it works, not able to get to it soon.

Look forward to the new "force" option in JMP 15.

Thanks!,
DS

Re: Force import format of Excel date column

SDF1 — Mon, 29 Apr 2019 18:50:19 GMT

Hi @gzmorgan0,

I've finally gotten back and been able to test out your JSL script that uses VB to save as a txt. It works, and I can import the information as needed, my only problem is that it only does it for the last tab in the Excel file.

My Excel file has multiple tabs that I need to import, all with the date problem. Once in JMP, I concatenate all the data into a single data table for analysis. I also just found out that I will be needing to modify the JSL to open multiple of these Excel files and then put all the data together. An added level of complexity.

Unfortunately, I'm not familiar enough with VB to write something that handles multiple tabs. I have a colleague who is though, and they're working on it.

I've tried changing my preferences in JMP to see if that fixes the issue, but it doesn't. There definitely seems to be a bug that even though I set my OS date format to Germany (d.m.y), it still uses the install OS (English) and reads the date as m.d.y, so it just ignores any day greater than 12.

The JSL script is very helpful, thanks for posting it. We'll just need to modify the VB portion to deal with multiple tabs, that's all.

Re: Force import format of Excel date column

dconradgreen — Mon, 20 Jul 2020 13:00:11 GMT

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( "-" )
    )
);

Re: Force import format of Excel date column

txnelson — Fri, 17 Jul 2020 21:43:58 GMT

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 );