600819_en-US

liliconcom

Re: problem importing date from excel

Good morning @Francoise ,

 

Your first date column on the Excel file only contains numeric values in date format.

 

The problem with the second column seems to relate to the Excel formula contained in the cells. There is a formula referring to a B:\ location and another Excel file in that column, so it doesn't directly contain the values in the correct format. A way to ensure this is to copy this column to Excel and perform a "special paste" with the values only on another column, which gives the same result as what you have on JMP (value 44927 without specific format) . As a result, the scripts carried out afterwards relate either to an Excel formula not taken into account in JMP (and give a "missing value" result: the points), or a value result as one can have in Excel by copying only the values ( :44927).

 

To correctly import this column, it would be better to perform a join with the source file and/or avoid formulas in the cells.

 

Hope this answer helps you,

Re: problem importing date from excel

Morning,

 

Thank you for that answer.

in fact, I have 2 databases (2022 and 2023).= calendar year database.

for the message, I copied and pasted the 2 columns in an excel file for example.

I have no problem with 2022, on the other hand with the 2023 database, I cannot import the date in the correct format despite several format attempts and by formatting the date columns in the 2 excel databases in the same format.

the result I show is the same as when I import directly from BDD 2023.

 

cdlt

Re: problème importation date à partir d'excel

From what I see, the first column Input date format is not standardized from the Excel file.

Maybe you can standardize the first column Input date before import.

Edit:

I faced similar problems.

I noticed that Excel can read both numeric dates.

I found out if I divide the JMP Numeric "44...." with factor "0.0000119633880658225" --> then convert to Date M/D/YYYY hh:mm:ss AM/PM, I get almost similar date with what Excel read (but not exact).

This happens when there's a break in the rows of dataset. In my case, there're few empty cells between rows.

I'm sure there're some calculation on this but I couldn't figure out.

 

 

problem importing date from excel

Morning,

 

I have this excel file with 2 date columns.

 

when I import directly into Jmp, the preview gives this:

when I import, I have this:

if I go through a script like this:

Names Default To Here( 1 );
 dt1 = Open(
   "/E:/Pb date.xlsx",
   Worksheets( "Feuil1" ),
   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( "-" )
   )
  );


Column( dt1, "Date de fab 2022" ) << data type( Numeric ) << Modeling Type( Ordinal ) << Format( "d/m/y", "-1", "NO", "" );
Column( dt1, "Date de fab" ) << data type( Numeric ) << Modeling Type( Ordinal ) << Format( "d/m/y", "-1", "NO", "" );

I get this:

I tried by changing the formats in the script. I did not succeed in importing the second column date in numerical format, continue.

 

I don't understand.

 

if anyone has the solution....

 

Cordially.

Advanced Statistical ModelingAutomation and ScriptingData AccessMass CustomizationQuality and Process Engineering