Import&modify excel worksheet from jmp table columns

joshua

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"  fill the rest of the rows of "Protection" and "Doors" to columns "LabelB" and "LabelB_numbers".

 

 

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

 

 

record_table.xlsx

txnelson

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