364258_en-US

liliconcom

Import&modify excel worksheet from jmp table columns

Hi,

 

I'm trying to modify excel sheet with the data from jmp table. Lets say our excel sheet in this format 

and we want to fill columns "LabelA" and "LabelA_numbers" columns when "D/P" rows == "Driver" with "Protection" and "doors"

and when "D/P" rows == "Passanger" with rest of the rows of "Protection" and "Doors" columns

 

 

so the final excel sheet should look like 

 

 

dt = open("$SAMPLE_DATA/Cars.jmp")


formula_Sheet = open("C:\record_table.xlsx",
 
 Worksheet Settings(
                                            1,
     Has Column Headers( 1 )
 
));

 

 

How can we do this ?

 

Thanks

Joshua

 

 

Data TableOtherScriptingRe: Import&modify excel worksheet from jmp table columns

Here is a script that gives you what you want.  I am not sure it is the approach you want to take, but given your starting point, and your described ending point, I believe it is probably the most efficient way to solve the problem.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Cars.jmp" );

formula_Sheet = open("C:\record_table.xlsx");
//formula_Sheet = Open( "$DOCUMENTS\discussion group\record_table.jmp" );
formula_Sheet << delete columns( {"labela", "labela_numbers", "labelb", "labelb_numbers"} );

dt << select where( dt:name( "D/P" ) == "Driver" );
dtA = dt << subset( invisible, columns( :Protection, :Doors ), selected rows( 1 ) );
dtA:Protection << set name( "LabelA" );
dtA:Doors << set name( "LabelA_numbers" );

dt << select where( dt:name( "D/P" ) == "Passenger" );
dtB = dt << subset( invisible, columns( :Protection, :Doors ), selected rows( 1 ) );
dtB:Protection << set name( "LabelB" );
dtB:Doors << set name( "LabelB_numbers" );

dtJoin1 = dtA << Join( invisible, With( dtB ), Merge Same Name Columns,By Row Number );

Close( dtA, nosave );
Close( dtB, nosave );

dtFinal = formula_sheet << join( with( dtJoin1 ), Merge Same Name Columns,By Row Number );

Close( dtJoin1, nosave );
Close( formula_sheet, nosave );

Re: Import&modify excel worksheet from jmp table columns

Thanks Jim,

 

First of all when I read the record_table.xlsx I'm getting the table like this

and after running the script getting this 

one anothe thing is that lets say if I want to change City column row 2 = "panama" with "colombia" how can I do that ?

 

Thanks