Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
jerryspilTC
Level III

Time format changes after importing excel

Hi,

 

I'm opening an excel directly in JMP, but after import the time format deduct the hh:mm:ss.

Table below from excel but in JMP table its only 2018/08/0X after import..

 

TEST_START_TIME   TEST_STOP_TIME

2018/08/03 17:54:02 2018/08/03 19:25:36
2018/08/03 19:25:36 2018/08/03 19:28:04
2018/08/03 21:05:08 2018/08/03 21:07:39
2018/08/05 20:52:09 2018/08/05 20:53:17
2018/08/05 20:52:09 2018/08/05 20:53:17
2018/08/05 20:52:09 2018/08/05 20:53:17

 

 

 Script to open an excel: (I dont think its related to my script but to the setting of importing an excel file, but I cannot find in preferences) Unless I overlooked it, have tried manual importing w/o script still same. Please advise thanks!

 

Names Default To Here( 1 );
Wait(3);
Close All (Data Tables, Nosave);
Close All (Journals, Nosave);
 
mypath = "C:\jmp\";
Set Current Directory( mypath );
y = Files In Directory( mypath );
For( i = N Items( y ), i > 0, i--,
  If( Ends With( y[i], "txt" ),
  ,
  Remove From( y, i )
  )
);
n = N Items( y );

For( i = 1, i <= N Items( y ), i++,
  sname=left(y[i],length(y[i])-4);
/* If the file is the first in the list, open it. Otherwise, open the table, concatenate with the main table, and close the table just opened. */
    If( i == 1,
        mainDt = Open( mypath || y[i] );
        mainDt << New Column ("source", character, set each value ( sname )),
        dt = Open( mypath || y[i] ) ;
        dt << New Column ("source", character, set each value ( sname ));
        mainDt = mainDt << Concatenate( dt, Append to First Table );
        Close( dt, NoSave );
        Wait( 0 );
    )
  );  

 

 

jerryspilTC
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
jerryspilTC
Level III

Re: Time format changes after importing excel

Errata: If( Ends With( y[i], "xlxs" ),
jerryspilTC

View solution in original post

Highlighted
gzmorgan0
Super User

Re: Time format changes after importing excel

Attached is the Excel file as you specified. Below is a script that opened the file and modifies the display format. Hope that helps.

 

Names Default to Here(1);

dt = Open("C:/temp/Excel_DateTime_Aug2018.xlsx");

dt:TEST_START_TIME << Format( "y/m/d h:m:s", 22, 0 );
dt:TEST_STOP_TIME  << Format( "y/m/d h:m:s", 22, 0 );

View solution in original post

3 REPLIES 3
Highlighted
jerryspilTC
Level III

Re: Time format changes after importing excel

Errata: If( Ends With( y[i], "xlxs" ),
jerryspilTC

View solution in original post

Highlighted
gzmorgan0
Super User

Re: Time format changes after importing excel

Attached is the Excel file as you specified. Below is a script that opened the file and modifies the display format. Hope that helps.

 

Names Default to Here(1);

dt = Open("C:/temp/Excel_DateTime_Aug2018.xlsx");

dt:TEST_START_TIME << Format( "y/m/d h:m:s", 22, 0 );
dt:TEST_STOP_TIME  << Format( "y/m/d h:m:s", 22, 0 );

View solution in original post

Highlighted
jerryspilTC
Level III

Re: Time format changes after importing excel

wow it works... thanks gzmorgan for your great help..

Names Default To Here( 1 );
Wait(3);
Close All (Data Tables, Nosave);
Close All (Journals, Nosave);


mypath = "C:\jmp\";

Set Current Directory( mypath );

y = Files In Directory( mypath );

For( i = N Items( y ), i > 0, i--,

If( Ends With( y[i], "xlsx" ),

,

Remove From( y, i )

)

);

n = N Items( y );


For( i = 1, i <= N Items( y ), i++,

sname = Left( y[i], Length( y[i] ) - 4 );

/* If the file is the first in the list, open it. Otherwise, open the table, concatenate with the main table, and close the table just opened. */

If( i == 1,

mainDt = Open( mypath || y[i] );
mainDt:TEST_START_TIME << Format( "y/m/d h:m:s", 22, 0 );
mainDt:TEST_STOP_TIME << Format( "y/m/d h:m:s", 22, 0 );

mainDt << New Column( "source", character, set each value( sname ) );
,

dt = Open( mypath || y[i] );

dt << New Column( "source", character, set each value( sname ) );

mainDt = mainDt << Concatenate( dt, Append to First Table );

Close( dt, NoSave );

Wait( 0 );

)

;

);
jerryspilTC
Article Labels

    There are no labels assigned to this post.