topic Re: Import&modify excel worksheet from jmp table columns in Discussions

Import&modify excel worksheet from jmp table columns

joshua — Sat, 10 Jun 2023 23:26:45 GMT

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

Re: Import&modify excel worksheet from jmp table columns

txnelson — Tue, 02 Mar 2021 08:07:37 GMT

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

joshua — Wed, 03 Mar 2021 06:21:35 GMT

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

Re: Import&modify excel worksheet from jmp table columns

txnelson — Wed, 03 Mar 2021 06:29:36 GMT

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.

Re: Import&modify excel worksheet from jmp table columns

joshua — Wed, 03 Mar 2021 16:20:06 GMT

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

Re: Import&modify excel worksheet from jmp table columns

txnelson — Wed, 03 Mar 2021 16:34:20 GMT

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.