- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Excel date import problem
Good morning,
I can't import the correct dates from an Excel file.
I formatted the Excel date column in the correct format.
I tried several date formats in Jump.
Jump does not import all dates and not in the correct format.
in yellow: the excel file.
does anyone have a solution?
Sincerely
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: problème import date excel
Difficult to say without having the actual files. Perhaps you could put the Excel file here, but only with the first two columns? Also, how are you trying to get the data into JMP? Are you using the Excel wizard? That will allow you to try different things to ensure that you get the data to come in properly.
My best guess is that the format of the dates is causing issues. It looks like you have this as dd/mm/yy. However, the first 16 rows or so could easily be interpreted as mm/dd/yy format. JMP is likely picking up that and setting the rest of that column to that format. In JMP, try looking at the column properties for your date column. Make sure that the format and the Input format are both d/m/y. Perhaps that will solve the issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: excel date import problem
Good morning,
Thank you for your prompt response.
I'm using the Excel import wizard and getting the import script to build dashboards.
the problem Excel file belongs to a group of 10 Excel files that have the same function (= analysis calibration file for different materials) . I have no problem with the other 9 files to import the dates.
I did some column formatting tests for excel and Jmp (I've been using Jmp since version 3).
I keep checking for the excel file....
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: problème import date excel
Hi Francoise,
It looks like the dates that do not fit the US date format (Month/Day/Year) are ignored because your input is in an international format (Day/Month/Year). As Dan suggested, I recommend using the Excel import wizard to help JMP figure out the date format.
Also, when I encounter similar problems, I import the dates as text and then change the column format to Numerical with an Input Format "dd/mm/yyyy" and an output format (Date) of your choosing.
Let us know if that helps.
Best,
TS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: excel date import problem
Hi,
should I do this:
Column( dtMAT, "Date" ) << data type( Character ) << Modeling Type( "Nominal" );
Column( dtMAT, "Date" ) << Set name( "Date origine XLS" );
Col = New Column( "Date" ) << Set data type( Numeric ) << Set Modeling Type( "Continuous" ) << << Input Format( "dd/md/yyyy", 15 ) << Output Format( "dd/mm/yy" ) << Formula( (:Date origine XLS) );
JMP imports all dates in nominal character but not possible to have continuous number format
cdlt
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: excel date import problem
Hi,
problem solved with "Informat":
Column( dt, "Date" ) << Data Type( "Numeric", Informat( "d/m/y" ), Format( "d/m/y" ) ) <<
ModelingType("Continuous");
Cordially
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .