BookmarkSubscribe
Choose Language Hide Translation Bar

Re: Force import format of Excel date column

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

Brian
0 Kudos
Highlighted
gzmorgan0
Super User

Re: Force import format of Excel date column

@DS, 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.

 

 

0 Kudos
DS
DS
Community Trekker

Re: Force import format of Excel date column

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.

0 Kudos
gzmorgan0
Super User

Re: Force import format of Excel date column

@DS, I have two suggestions:

  1. 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.
  2. 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.

0 Kudos
ghartel
Community Trekker

Re: Force import format of Excel date column

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

 

 

 

0 Kudos
gzmorgan0
Super User

Re: Force import format of Excel date column

@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." 

 

DS
DS
Community Trekker

Re: Force import format of Excel date column

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
0 Kudos
DS
DS
Community Trekker

Re: Force import format of Excel date column

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.

0 Kudos