topic Re: Excel Import Date Format in Discussions

Excel Import Date Format

vkessler — Fri, 29 Jan 2016 16:24:44 GMT

Hello everyone,

i want to import an Excel-File ( xls ) with the Open() function. Here is my code:

dt = Open(

"path\file.xls",

Worksheets( "worksheet1" ),

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( 5 ),
	Data Starts on Row( 6 ),
	Data Starts on Column( 2 ),
	Data Ends on Row( 0 ),
	Data Ends on Column( 0 ),
	Replicated Spanned Rows( 1 ),
	Suppress Hidden Rows( 1 ),
	Suppress Hidden Columns( 1 ),
	Suppress Empty Columns( 1 ),
	Treat as Hierarchy( 0 ),

);

The 2nd column in the xls-file ( Column 2 <--> Data Starts on Column(2) ) is filled with dates in the format dd.mm.yyyy. The issue is now, that the import doesn´t work correct, since it imports the dates in the format mm.dd.yyyy.

Is there a way to import the dates in the approbiate format, i. e. dd.mm.yyyy ?

Thank you very much!

Re: Excel Import Date Format

briancorcoran — Fri, 29 Jan 2016 20:43:54 GMT

Hi,

If the data has a format applied to it within Excel, then JMP 12 should import it properly. I just tried a case. However, if it is typed as General or m/d/y, then it is going to be imported as m/d/y if you are running Windows or Mac under United States settings.

The alternative is to go into Excel, set the format for the column (Right mouse column header, format cells...) and set the locale to somewhere where d/m/y is the default. You can then select the correct format and resave the file.

If the data comes in as Character because JMP couldn't get a format from Excel, it should be easy to change it to Numeric Date with the format that you desire.

Regards,

Brian Corcoran

JMP Development

Re: Excel Import Date Format

vkessler — Mon, 01 Feb 2016 08:39:49 GMT

Hi Brian,

thank you for the quick anwser. Setting the date column in excel to a fixed date format (not sensitive to local settings) did the job.

Regards,

Victor

Re: Excel Import Date Format

ghartel — Tue, 16 May 2017 00:26:14 GMT

I have the same problem, but I have dozens of files and would like to handle this within a script and without editing source data files. JMP reads in most of hte files fine but occassioanlly if the first few rows have ambiguous formats, ie day <= 12, then it reads the format in as mm/dd/yyyy and any dates where day>12 just goes missing. the date format within Excel is correct, ie dd/mm/yyyy. If it were a CSV file I could specify the format of specific columns. Is that possible for JMP?