Import&modify excel worksheet from jmp table columns

[#1]

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

[#2 · Accepted solution]

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

[#3]

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

[#4]

There are several ways to make the change you are asking, but the most direct way would be

:City[2] = "colombia";

I strongly suggest you take the time to go through the JMP Scripting Guide.  It is available in the JMP Documentation Library found under the Help pull down menu.

[#5]

yes belive me I'm doing that. But its not good documentation for what I want to do. too general stuff. That's why we have this forum to ask things we cannot find in manual. For example in the manual it does not explain why I'm getting Column 1 , Column2 ... etc instead of real column names of the excel sheet.

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

[#6]

You should use the JMP Excel Wizard to open your file once, and then go to the Source entry in the Data Tables "Table Panel" at the upper left hand side of the data table, right click on the green arrow and select Edit.  It will show you the JSL to use to properly read in the table you are reading in.