66820_en-US

liliconcom

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 );
    )
  );  

 

 

Re: Time format changes after importing excelwow 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 );

)

;

);
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 );

Re: Time format changes after importing excelErrata: If( Ends With( y[i], "xlxs" ),